Creating a "complicated" run-rate chart from MS Project Data #2

[Pete Mccann]

This is a continuation question following on from the last message on the post "Creating a "complicated" run-rate chart from MS Project Data". https://chandoo.org/forum/threads/creating-a-complicated-run-rate-chart-from-ms-project-data.36418/
p45cal provided a solution which works very well to colour individual parts of the chart but I am trying to extend the range of this solution to approximately 100 lines of data. I think the VLOOKUP part of the calculation has got corrupted and I'm not sure what I have done wrong. Cell A2 gives a Total of the quantities being analysed and Column J should have the same total so that the sum and the % calculations work correctly. I have made some changes to the original VBA to change the range where the data is (now it is B4:F100). Do I need to have data in the full range B4:F100? Can anyone see what I've done wrong?

 

[p45cal]

Your source data is coming in (or is massaged) diffferently from how you had it before.

• A date in column C would be followed in the row below with the same date in column B, now you often have it so that the next row's date in column B is one day after. This shouldn't matter too much, if at all. It's just that it will be easier for you to keep the data as it is (as it comes in from MS Project) so that there's less chance of errors creeping in. You'll need to tell me which version of the data is closer to the original data. Whichever it is, formulae can correctly deal with it.
  
• You said in your Word document that there would be no overlap of dates of tasks; this time you do. Tests A and B both start on the same date. In this particular instance, this is NOT causing the problem because they both have zero Qty associated with them. It would have been problematical if they didn't.
• The 4th argument of the VLOOKUP formula used is TRUE, for an approximate lookup (that's what MS call it) and in this case for it to work correctly the dates in column B need to be in ascending order; tests G and H are not in the correct order.

Come back with comments on the above 3 points to decide on which way best to move forward.

[If you sort your top left table by Start date and tweak Test G's Finish date down to 15/8/2018 (to follow the convention above it) your chart should colour correctly.]

 

[Pete Mccann]

Hi p45cal. Yes, there was a bit of massaging of the data because I was working on real information in the recent example I posted.
The attached spreadsheet is very close to what we would like to do.

Each of the three sheets contains a Copy paste from a MS Project POW (Initial “Contract” and two revisions “Rev_0” and “Rev_4”). Rev_4 is the latest POW. The sheets contain real start / finish and qty data but I’ve removed the specific information for each row. The cells in Bold are summary tasks from MS Project. I have manually deleted the start and finish dates of these summary tasks (but I don’t think this actually matters when creating the chart)

There is really only one thing that does not work correctly. When an Activity Starts and Finishes on the same day but has a Quantity >0 associated to it, this quantity is not recognized by the VLOOKUP() in the “per day” columns. This can be seen in the Green cells in the Rev_0 sheet (7 examples). This in turn makes the total % figure < 100%.

In the “Rev_4 POW” data, there are only 4 examples of where an activity Starts and Finishes on the same day but has a Quantity >0 associated to it. In the Rev_4 example, I have manually changed the start date to be 1 day earlier than originally planned. This fixes the problem and gives us 100%.

Also on the “Rev_4” sheet, I have linked the Rev_4 data with the Rev_0 and Contract Data. I had to do a small correction in cells I4 to make all the minimum dates equal so the chart works correctly.

I have tried to change the IF statement (J4) to become an IF/AND statement to try to take into account the problem of where an activity Starts and Finishes on the same day but has a Quantity >0 associated to it but I'm not sure this is possible.

Do you have any further ideas?

 

[p45cal]

So these two rows both represent 1 day's duration and, crucially, that's how the data comes in from MSProject?:

If so, this is going to require a rethink.

Also on the “Rev_4” sheet, I have linked the Rev_4 data with the Rev_0 and Contract Data. I had to do a small correction in cells I4 to make all the minimum dates equal so the chart works correctly.

Can you point me to the cells where these links are? I haven't found them yet.

 

[Pete Mccann]

Hi - attached file shows the same data extracted from MSP but I have shown the dates with their associated time elements. Most fractional days are 0.5days but there is one that is 0.2day.

Sorry, my comment about "linking the Rev_4 data with the Rev_0 and Contract Data" is misleading. I meant that I added columns M and N to the Rev_4 sheet so I could bring across the data for the Contract and Rev_0 plans such they are placed adjacent to the Rev_4 data - I found that this was an easy way to create the chart with three series - only the Rev_4 series needs to be coloured - the other two lines are shown as a reference / baseline.

 

[p45cal]

This begins to make sense.
In the attached, take a look at Sheet1 (2) and tell me if the chart nearly gives you what you want (they're not percentages and the start date is wrong).
If so, then can you confirm the following that I've done is OK:
1. Deleted all rows that contain the word summary anywhere in the cell in column B. (I realise it might be easier to assign tasks to the various activities before this is done.)
2. No need to calculate a per day Cable_km (this is the same as Qty?) since we're plotting on an XY chart so we don't need to calculate a point for every day. This will make the whole thing a lot simpler.

Note that one of the things I'll be checking plot correctly are data from the likes of Activities 51 and 53 (the is no Activity 52) which have a gap between one's finish and the other's start. Here I don't think it'll affect the plot since there are no Cable_kms involved.

If these are OK, then, at the moment, I don't see why we can't plot 3 or more similar charts with data from different sheets on top of each other, only colouring the line from the current sheet.
So if all the above is more or less correct, can you send me raw data for the 3 or more revisions of the project, and if you want, assign the tasks in another column, but no deleting or amending of the raw data. Just so that I can experiment.

As a complete aside, yor data is copy/pasted from MSProject; is there not an export facility or save as option to get this data?

 

[Pete Mccann]

Many thanks for your help so far.
Yes, the shape of the plot on Sheet1 (2) looks good.
"1. Deleted all rows that contain the word summary .." - yes, we have realized that this data is irrelevant for the chart.
"2. No need to calculate a per day Cable_km (this is the same as Qty?)" - no, it is important that we can plot the Cable_km per day since we measure Cable_km per day .... so we need to do a comparison with the actual installation (the actual km/day is our progress measure and this would be another series on the chart - sorry, I should have mentioned this earlier (but this is easy to do) ..).

Activity #52 missing - my fault, finger trouble on the keyboard. There should be an Activity #52 that would be 16/08/2018 04:48 to 22/08/2018 04:48.

My main job is working with MS Project. I know that there is an export feature in it and we are looking at similar problems to export data from MSP (or to import data from MSP directly into Excel) - this is a different topic I'm working on but the aim is very similar to what we are discussing here....taking data from MSP and preparing reports or charts in Excel.

Here is a set of raw data. It might be slightly different to the previous set because I had to regenerate some of the information.

 

[p45cal]

Initial trials seem to show that it's straightforward to plot data from three different sheets, with different date ranges onto a single chart and they line up as they should.

However, when plotting your raw data like this I came acrosss some unexpected values: In your most recently posted file, cells B10:C10 dates seem out of step, as does cell O5's date. Can you confirm that these are correct?
This is what prompted me to check:

Also is it correct that the Contract data starts in late Feb (this causes the chart above to have a lot of empty space on the left)?

You said:"no, it is important that we can plot the Cable_km per day since we measure Cable_km per day .... so we need to do a comparison with the actual installation (the actual km/day is our progress measure and this would be another series on the chart - sorry, I should have mentioned this earlier (but this is easy to do) ..)."

So when you're doing the comparison, is it just a visual comparison on the chart, which your progress measure series would show? In which case we still won't need to plot a point for each day for the other lines; only for the actual progress line.
If it's to see a numeric comparison (expressed as a percentage or as Cable_kms) then those can be calculated easily, point by point as required - it's just simple arithmetic to interpolate a point, or there's any one of 3 built-in Excel functions that can be used to do that.
I'm just trying to make things as simple as they can be to get you what you need, the simpler the less chance of errors creeping in, so that future reports you make are both accurate and quick to produce.

 

[Pete Mccann]

Thanks. Here is a slightly modified file reflecting your comments. B10:C10 removed (it didn't need to be there). B4:C6 also deleted to remove the dead space at the start of the chart. N5:O5 converted to Activity summary (so would ultimately be ignored in the analysis). We still need to plot the cable_km per day but we can ignore the progress series on the chart.

 

[p45cal]

We still need to plot the cable_km per day but we can ignore the progress series on the chart.

I've done a daily plot in the attached but it comes with major health warnings;

• Whereas the plots offered in msg#6 above could easily be plotted on a chart (to the minute) and coloured according to their task category, a daily plot (one point per day) has frequently to incorporate several tasks in one daily point; this makes colouring the chart very difficult.
• Inaccuraceis are brought in. Instead of to-the-minute plots, you only have to-the-day plots.
• It doesn't happen with your data (so far) but if there were to be distinct tasks on the same day, both contributing to Cable_kms, it'll be impossibe to show them separately. This differentiation happens automatically with my previous suggestion.

That said, in the attached, which is your latest file, whose data has been left untouched, there's a blank chart. This chart will be populated on clicking the buttons in the vicinity of cells U1 and Z1. These buttons run their own macro (they're exactly the same except for where they fetch data from and where they dump their results).
Each macro picks up the data from one of your tables, disregards rows with 'Summary' in them, and produces a daily table to plot.
The Date column contains every date from start to finish.
The Cable_km column contains the number of kms for that day, from as many activities that are active that day, apportioned pro rata with the entire duration of that activity, down to exact proportions of a day. For example, if an 70km activity has 3.5 day's duration, and the first day of that activity is only half a day, then only 10km will be assigned to that first day, the other three days getting 20kms each.
The Cum.Cable_km column is just the cumulative of the previous column.
The Task(s) column I've not worked out yet how best to populate, currently, it just concatenates any tasks that fall on that day.

In the attached, I've highlighted cells J71 and J73. Those rows and the row inbetween all have 11th Aug in them and are three different activities. I've also highlighted where that data ends up; T61:W61. You can play with it by adding Cable_kms to the cells J71 and/or J73, click the button and observe how that affects the output.

Is this a way forward? I hope not! (You could use this to give you a day by day progress target, but I would hate to plot and colour it, as each day's point cannot safely be ascribed to a single task.)

 

[Pete Mccann]

Thanks. In the real world, the quality of the information that gets into the spreadsheet depends on how carefully the MS Project POW is put together. We try to "insist" that the persons creating these POWs provide sufficient detail such that any task involving Cable_km is separated from other tasks that do not involve Cable_km. So, even if a 24h period contains, for example, 6h non-cable activity (Task X) +12h Cable_km activity (Task Y) +6h non-cable activity (Task X), these three elements would appear as 3 separate lines in the POW (and then 3 separate lines in the spreadsheet). We are really only interested in the 12h Cable_km activity in terms of the plot. The main idea behind these plots is to give a quick visual image of how the POW has changed (from, say, Contract through Rev_0 to Rev_4 etc.). We will not be using the chart to analyse the information. So, from that point of view, we don't need anything more detailed than a "per day" view. So, to answer your question..."Is this a way forward?.." - no, we do not need to follow this route (but the macros that you've created are very useful). From my point of view, the version of the file created in msg#3 earlier is workable. If there are any constraints with respect " ..an Activity Starts and Finishes on the same day but has a Quantity >0 associated to it, this quantity is not recognized by the VLOOKUP() in the “per day” columns" we can detect them with the calculations in U1:U4 and then do a manual adjustment of a start date to overcome the issue. As I said before, the main idea is a quick visualization of the data. I'm really grateful for your help on this topic but I don't wish you to waste any more of your valuable time on it......