Tips on using this forum..

(1) Explain your problem, don't simply post "This isn't working". What were you doing when you faced the problem? What have you tried to resolve - did you look for a solution using "Search" ? Has it happened just once or several times?

(2) It's also good to get feedback when a solution is found, return to the original post to explain how it was resolved so that more people can also use the results.

Date Variance Trends

26 replies [Last post]
Rafael Davila
User offline. Last seen 6 hours 48 min ago. Offline
Joined: 1 Mar 2004
Posts: 4616

When dealing with Date Variance it might be convenient to have the following options.

  • Days vs Hours - some jobs are scheduled by the hour, some jobs use workdays with different hours. 
  • Date difference vs. Activity Calendar - at times it is of interest to follow date difference, at times it makes sense to follow difference using activity calendar, at times it is good to compare both.DVT01

This can be done if using user defined fields and some formulas but I wonder if there is a direct way I might be missing.

Best Regards,

Rafael
 

Replies

Rafael Davila
User offline. Last seen 6 hours 48 min ago. Offline
Joined: 1 Mar 2004
Posts: 4616

For a single activity diagrams, templates and table reports works as expected.  I take no issue on the diagrams when dates are mixed with other values, just create separate diagrams for a better view.

Date-Trends-Template

I am having issues when multiple activities are selected and with general reports. First version values are still missing; perhaps timescale could be expanded before project start to make some room for the display [something is shown]. I understand this is ongoing improvement. 

To be able to pull down all this data from multiple schedule versions and get automatic variance calculations is good, very good.

Trends

Rafael Davila
User offline. Last seen 6 hours 48 min ago. Offline
Joined: 1 Mar 2004
Posts: 4616

I wonder why the formatting always by column instead of by parameter.

  • If parameter on row then selecting a row should open the formatting box to set the number format of all rows containing the parameter.
  • Selecting a column would open the formatting box only to set the column width for current/all columns if parameters on rows.  If parameters on columns then also the number format would be available.
  • Such formatting should also be available on the report templates.
  • Perhaps there could be other formatting options/[Categories] for Cost fields such as Currency and Accounting; For Numeric fields Number and Percentage and separate category for text fields.
  • Additional software calculated fields for Start and Finish could be created such as Start[value] and Finish[value].
  • Subtotals/Totals then by parameters, executed only for any type except for date-time and text fields, if organized by row then Subtotals/Totals shown on row, if by columns then Subtotals/Totals shown on columns.

Am I getting something wrong?

Rafael Davila
User offline. Last seen 6 hours 48 min ago. Offline
Joined: 1 Mar 2004
Posts: 4616

Figured it out how to make the transfer, easy. 

  • Create UDF Start100 and Finish100; Set as Date Format, Copy-Paste Date Values, Change to Numeric Format
  • Execute Variance Report for UDF Start100[value] and Finish100[value], Change Columns format to Date
  • Transfer to Excel and make your diagrams as well as other calculations such as Start/Finish[date variance] as a date difference in addition to Start/Finish[workdays variance].

Date-Value-Trends01

Rafael Davila
User offline. Last seen 6 hours 48 min ago. Offline
Joined: 1 Mar 2004
Posts: 4616

When we use two vertical scales in Excel we frequently use different Chart Types, usually lines & columns.  Values and variance look better if using different scales.  Maybe General Trend Parameters could be split to Numeric Parameters & Date Parameters, then variance shown as columns and values as lines similar to following figure.  An option can be to allow Date Parameters only on UDF selection, by means of simple formula any Date Parameter might be available.

  • For Date Parameters (Start&Finish) value scale will show dates and variance will be numeric. 

Data-Types26

  • For Numeric Parameters (Duration, Float, Resource-hours, Total Cost & Volume) value scale as well as variance scale will be numeric but scaled different. 

Data-Types27

  • Maybe User Defined Fields should be split into Numeric UDF & Date UDF similar to General Trend Parameters, all UDF available on both.  Selection will determine data type of value axis. Values of the other type will be ignored and shown empty warning the user there is a mismatch, a warning might be issued after every re-create if this happens for user decide and fix.  To show current version parameter type on Diagram selection box should help.

Variance-Trend-Parameters

  • Another option, I can figure my combination charts if using Excel, but I need meaningful dates.  If you give me a function to convert the start/finish values to dates within the tabular report I will have dates in my tables as well as in my Excel charts.

Uper left looks satisfactory but requires to show table together with the diagram.

This may be done.

Rafael Davila
User offline. Last seen 6 hours 48 min ago. Offline
Joined: 1 Mar 2004
Posts: 4616

Data-Types23

The following I can also understand.

Data-Types24

Let's suppose that one trend is 0 - 1 -3 -1 and another one 75 -76-78 -76.

It will not look nice with numbers and even less attractive with dates.

Rafael Davila
User offline. Last seen 6 hours 48 min ago. Offline
Joined: 1 Mar 2004
Posts: 4616

• The key is that for some lines, say solid lines, the scale are numeric values, for other lines, say dashed lines, the scales are Dates. Each vertical scale adjusted independently.

These shown values for date scale as well as for numeric scale need not to be aligned with every point. In most cases 4 to 6 values will be showm.

Dates that are shown with a number I am still wondering what they are I will never use. When mixed with numeric values it gets even worse. Date-Variance-Trends03

On the background Excel use numbers to adjust Date Scale. Excel displays the Dates not the numbers.  Auto adjust did not adjusted Dates to their minimum preventing the lines to be on top of each other.  It is all about how to display date values, what values or numbers are used on the background do not matter to the average end user. 

Format-Axis

Numbers show delays in days and delays of start and finish may be easily shown in the same diagram. Dates of start and finish may be very different, trends will start at different points and inserting readable dates is a problem. I think that values report shall be left in the table In any case the table may be exported to Excel for creating other presentations.

Rafael Davila
User offline. Last seen 6 hours 48 min ago. Offline
Joined: 1 Mar 2004
Posts: 4616

The vertical tables shall adjust automatically based on the range of values, from min to max value; the numbers will be shown in the diagram table as well as in the tabular reports. If user needs to get a more detailed diagram he will always be able to create separate diagrams or use table reports.
• Similar to project phases, for long duration activities the difference can be months, take for example a single delivery activity.
• For many reasons such as a design change due to unforeseen conditions the start of an individual activity might be delayed from version to version. So even if looking only at start only the difference can be months.
• In diagrams in most cases I would prefer to display Date not Date-Time.
• The difference will create same problems if using a number I am still wondering what it means.
• The key is that for some lines, say solid lines, the scale are numeric values, for other lines, say dashed lines, the scales are Dates. Each vertical scale adjusted independently.
• Definitively phase trends do matter, same as for individual activities.
• Diagrams saved as a template will be available to generate tabular reports where this issue with vertical scales will not happen.
 

I don't see problems with adding version numbers in trend table reports but it is now clear if it makes sense to show dates in the diagrams.

Let's suppose that we want to show both start and finish dates trends. For project phases the difference may be several months. Showing dates in the Y axis may become complicated. I don't see easy solution.

Rafael Davila
User offline. Last seen 6 hours 48 min ago. Offline
Joined: 1 Mar 2004
Posts: 4616
  • Variance fields shall never be text fields, Spider Table Reports are not mere pictures but worksheets you can add formulas and export to Excel. 
  • If you add version number to value/variance you get a text field.
  • Because we only have Text, Numerical and Date-Time Field Types if we eliminate Text Type we are left with only two types, therefore all we need is two axis to cover all bases. 
  • One title row to show version number is enough. 
  • Templates shall be available for tables as well as for diagrams.
  • First version values could be shown on intermediate pane, other values on right pane. 
  • Make it clear if [Value] or if [Variance].

Start-Finish-Trends09

Bogdan Leonte
User is online Online
Joined: 18 Aug 2012
Posts: 150

In order to delimit versions why not use markers, each marker representing an update period?

Also I think it would be easier to realise the following: insert an option in the diagram/table report wich will, show the version in brackets after the value of the parameter, something like this 22.00 [V1].

See image: https://ibb.co/k2nvxA

Rafael Davila
User offline. Last seen 6 hours 48 min ago. Offline
Joined: 1 Mar 2004
Posts: 4616

Something like: (but with date values in table using dates format)

2-vertical-scales

After many updates if selected versions are not shown it can be difficult to figure it out.

Trend-Versions-Missing

Rafael Davila
User offline. Last seen 6 hours 48 min ago. Offline
Joined: 1 Mar 2004
Posts: 4616

- "We will need to forbid showing more than one trend in the same diagram."

  • May I say, we will need to forbid to show incompatible scales on same diagram and allow to show more than one trend as long as the scales (time vs. numeric) are compatible.
  • Another option can be to show to the left numeric scale and to the left of numeric scale a date scale. Similar to Excel but primary and secondary scales to the left.

Date-Variance-Trends03

Lines might be shown solid for numeric type and dashed for date type, or vice versa; this can be shown in the report legend so everything is crystal clear.

  • Maybe the report date format could be set to be inherited from the format chosen for their respective columns, to make it a better fit for the report purpose, a better fit for the scale of the job.
  • Agree this problem do/should not happen in table reports.

I am exploring what happens if a trend user defined field is set to numeric in one version and changed in another version to a a text type an issue not existing on standard fields.

  • I expect text values to never be pulled down on any trend report, I expect only values of the type (numeric/date) of first version fields will be pulled for user defined fields.

Yes, I understand. Look at the table report.

We think about doing this in the diagrams because there is a problem when you will want to show several trends together (like time and cost trends or even trends of start and finish).

Variances are values that can be shown but dates and numbers require different scales. Start and Finish may have large difference and showing dates on the same scale may become tricky.

We will need to forbid to show more than one trend in the same diagram.

Rafael Davila
User offline. Last seen 6 hours 48 min ago. Offline
Joined: 1 Mar 2004
Posts: 4616

Vladimir,
This is what I got.
Start-Finish-Trends01

  • Re Variance: While I cannot see the versions I can understand the variance. There is no way to know if a version is missing as the user can select any subset. I can see a date I guess is the first value, because calendars complicate things I cannot guess the remaining dates. 
  • Re values: I cannot see the versions I cannot understand the values. I see a lot of numbers but no dates.

This is what I would understand.

StartFinishTrends02

  • I can understand the variance, I can understand the date values, I can see selected versions aligned with the variance & values.

Best Regards,
Rafael
 

Rafael,

try values trends for starts and finishes in the new version.

Best Regards,

Vladimir

Rafael Davila
User offline. Last seen 6 hours 48 min ago. Offline
Joined: 1 Mar 2004
Posts: 4616
Vladimir,
  • Variance shows how selected parameter moved from the first version to the last. Baseline version may change but variance trends will remain the same or other history (a set of the previous versions) shall be selected.
    • I always show Baseline as my first version. - I did not notice such important behavior. Thank you for the clarification, no surprise you noticed my use of the term Baseline.
    • Diagrams do not display version numbers but only values. – Perhaps when selecting to show diagram values an additional row can be used to display version number.
    • Diagrams do not identify first version number – Perhaps Diagram Name can be set to show it. 
    • Tables identify version DD – It is the title Row, do not show version number.  I would like to see version number rather than a date. Maybe the labels could show compared versions; say [1,1],[1,2], [1,3] & [1,4] for following report, in case of a diagram [1,1] will not be needed.  For following report, in case of a variance row names could read Start[variance] & Duration,Days [Variance] as to make it clear to audience not familiar with Spider Project. VT08-Table
  • I do not understand Start[Value] - I was expecting to see dates for any date field. I believe by showing dates in the diagram as well as in the tabular reports it will make it easier to understand.
    • If I got it right with regard to [Value], that it is not a variance calculation, then the reports can show Value Trends as well as Variance Trends.  I suggest to name the reports "Trends" or whatever best fits.  Maybe Spider Project started with variance trends only and latter added values. Not sure but I am a bit lost with [Value].
    • I can see Value Trends as the ultimate values comparison tool as it is not limited to current plust two versions. All within a single table or diagram at a few clicks. 
  • The creation of variance reports documents is automatic.  To add formulas to any Spider Document is easy, easy to find the variance difference between any period. Variance-Report
  • To be able to pull down such values from different schedule versions perfectly aligned with the activities is a very powerful functionality. To be able to pull down variance values considering activity calendar adds to such very powerful functionality. In most instances impractical if using Excel alone; a monumental task to align activity codes in schedules with thousands of activities; a monumental task if calculating date difference considering activity calendars. Perhaps Power Query might help but it is complicated, not as easy as if within the scheduling software. 

Once again, thank you for your patience and tolerance with my requests.

Best Regards.

Rafael

Rafael,

Variance trends show what happened with the selected parameter with each (or selected) updates.

The results can be shown in the table form or as the diagram.

Start variance shows how planned start date moved from the first version to the last.

Baseline version may change but variance trends will remain the same or other history (a set of the previous versions) shall be selected.

We expected that people are interested in the movements of selected parameters and that is why do not show dates but changes in start/finish dates.

Rafael Davila
User offline. Last seen 6 hours 48 min ago. Offline
Joined: 1 Mar 2004
Posts: 4616

Vladimir,

I am having issues to understand Start [Value], can you please help, maybe they are just temporary values, but if so why show them? 

Variance-Values

Why not show the dates?

Start-Value-Trend03

  • Perhaps the report names should be "Variance[Value] Trends"  and "Variance[Baseline] instead of "Variance Trends" as both can be shown. 
  • Maybe a third option can be "Variance[Version] to show variance among schedule versions. all under the report label "Variance[Value,Baseline,Version]".

Report-Name01

Report-Name02

I suppose when you generate trends a table is created, in such case to make it visible will make it easier to export the values to Excel or to a Spider Document.

Best Regards,

Rafael

Rafael Davila
User offline. Last seen 6 hours 48 min ago. Offline
Joined: 1 Mar 2004
Posts: 4616

Great, I will always be able to create a UDF for hours for few schedules when need be, no big deal, but the Days/unit reminder can be handy for the diagram labels. 

I can also use date formulas to calculate difference using dates instead of activity work time.

Rafael,

we expected that when people look for trends of Start/Finish they are interested in dates and so show days as the only unit.

When people plan and analyze durations that may be interested in work days and hous.

Yes, adding days as measurement unit for Start and Finish trends is useful.

Rafael Davila
User offline. Last seen 6 hours 48 min ago. Offline
Joined: 1 Mar 2004
Posts: 4616

I am looking for Start and Finish trends.  I suppose variance can be in days as well as hours, similar to Duration.  Also variance can be measured as a date difference or as a calendar difference. Of course formulas and UDF can be used, as I currently do.  I am just wondering.  Perhhaps current Start/Finish should read "Start/Finish, Days" so it is cristal clear the unit.  

Something like:

  • Finish, Days
  • Finish, Hours
  • Start, Days
  • Start, hours

Trends-Unit

Duration trends are calculated in days or hours using activity (phase, project) calendars.

Dates difference shows time difference.