Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a chart that displays the following:
Line - Total Project Closed in a given month
Bar - % of those projects closed beyond the original due date
Now the issue that I am having is that they want to see this an a cumulative chart. So for instance in January we closed out 15 projects and 11 of them were past the original date so we have a percentage of 58% that are late. Now in February they closed out another 13 projects and 5 were late so that month has a 38% rate. They want to see Jan +Feb in the Feb chart so they would infact have 32 projects closed and 16 late for a percentage of 50% late.
I am struggling here.....Any help would be much appreciated.
Here is some of my data for an example
The "CompOrig" is essentially the actual completion date of the project - the due date and I count the project if that calculation is greater than 0. To get my percentage I take that field / total closed...
Month | =(Count(If(CompOrig > 0, Title))) | =Count(Title) |
---|---|---|
96 | 174 | |
Jan | 11 | 19 |
Feb | 5 | 13 |
Mar | 9 | 17 |
Apr | 9 | 11 |
May | 17 | 25 |
Jun | 9 | 17 |
Jul | 13 | 19 |
Aug | 2 | 17 |
Sep | 5 | 14 |
Oct | 11 | 16 |
Nov | 4 | 5 |
Dec | 1 | 1 |
Try this:
This is a straight table using expressions to work out accumulations rather than selecting accumulation in the table properties.
Accumulated Late =RangeSum(Above(Late,0,RowNo()))
Accumulated Closed=RangeSum(Above(Late,0,RowNo()))
Ratio is Accumulated Late/Accumulated Closed
Month | Late | Closed | =RangeSum(Above(Late,0,RowNo())) | =RangeSum(Above(Closed,0,RowNo())) | =RangeSum(Above(Late,0,RowNo()))/RangeSum(Above(Closed,0,RowNo())) |
---|---|---|---|---|---|
Jan | 11 | 19 | 11 | 19 | 57.89% |
Feb | 5 | 13 | 16 | 32 | 50.00% |
Mar | 9 | 17 | 25 | 49 | 51.02% |
Apr | 9 | 11 | 34 | 60 | 56.67% |
May | 17 | 25 | 51 | 85 | 60.00% |
Jun | 9 | 17 | 60 | 102 | 58.82% |
Jul | 13 | 19 | 73 | 121 | 60.33% |
Aug | 2 | 17 | 75 | 138 | 54.35% |
Sep | 5 | 14 | 80 | 152 | 52.63% |
Oct | 11 | 16 | 91 | 168 | 54.17% |
Nov | 4 | 5 | 95 | 173 | 54.91% |
Dec | 1 | 1 | 96 | 174 | 55.17% |