Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I need to get the cumulative percentage of the Module Assigned / Module Assigned (Completed) on a weekly basis. Something like I need to get the percentage of each row.
WeekEnd | Module Assigned | Completed | % Completion |
---|---|---|---|
6/19/2015 | 3 | 1 | 33.33% |
6/26/2015 | 274 (271+3) | 114 | 41.61% |
9/25/2015 | 280 (274+6) | 119 | 42.50% |
10/30/2015 | 282 | 120 | 42.55% |
11/6/2015 | 290 | 123 | 42.41% |
11/13/2015 | 344 | 127 | 36.91% |
Here's my expression for the Module Assigned and Completed (both in Full Accumulation already)
Module Assigned = count(Training)
Completed = count({$<Status = {'Completed'}>} Training)
Hi Edward,
I took your data and worked back to this table of non accumulated figures
WeekEnd | Module Assigned | Completed |
6/19/2015 | 3 | 1 |
6/26/2015 | 271 | 113 |
9/25/2015 | 6 | 5 |
10/30/2015 | 2 | 1 |
11/06/2015 | 8 | 3 |
11/13/2015 | 54 | 4 |
You can make a straight table with the Weekend as dimension, all other columns are expressions. Instead of using the accumulation feature of the straight table use instead the expressions here to calculate the accumulation then the last column is the ratio of the preceding two expressed as a percentage.
WeekEnd | Module Assigned | Completed | RangeSum(Above([Module Assigned],0,RowNo())) | RangeSum(Above(Completed,0,RowNo())) | RangeSum(Above(Completed,0,RowNo())) / RangeSum(Above([Module Assigned],0,RowNo())) |
---|---|---|---|---|---|
6/19/2015 | 3 | 1 | 3 | 1 | 33.33% |
6/26/2015 | 271 | 113 | 274 | 114 | 41.61% |
9/25/2015 | 6 | 5 | 280 | 119 | 42.50% |
10/30/2015 | 2 | 1 | 282 | 120 | 42.55% |
11/06/2015 | 8 | 3 | 290 | 123 | 42.41% |
11/13/2015 | 54 | 4 | 344 | 127 | 36.92% |
hope this helps!
You can use the below expression:
Count({$<Status = {'Completed'}>} Training)/Count(Training)
Hope this helps.
Hi Sinan,
I tried actually that expression but I'm not getting the correct results.
Do you think you can post a sample qvw or your expression?
Hi Edward,
For %Cumulative you can also use below exp:
=Column(1) /Column(2)
Regards,
Nagarjuna
can you share sample data and your expected out put .
Regards,
Nagarjuna
Hi Nagarjuna / Sinan,
Please see attached qvw file.
Data is under the Compass Training table.
Actually the data has no numerical data involved..Just the count of all the training I assigned per day and I want it actually to be grouped per week. Not sure how to do that in script.
Thanks,
Hi,
I'm not sure what a cumulative percentage means. The only sensible definition I can think of is the percentage ratio of two cumulative totals and that is what you have.
If I've misunderstood you I apologise.
Kind regards
Hi Andrew,
Sorry if my question wasn't clear enough. But what I need to have is the percentage of each row.
This is the Actual Count each week.
This is the Full Accumulation
What I need to get is the percentage of each row. So for example, I should be getting 42.70% (120 / 281) for 9/25/2015, 42.75% (121 / 283).
Hope that makes sense.
Hi Edward,
I took your data and worked back to this table of non accumulated figures
WeekEnd | Module Assigned | Completed |
6/19/2015 | 3 | 1 |
6/26/2015 | 271 | 113 |
9/25/2015 | 6 | 5 |
10/30/2015 | 2 | 1 |
11/06/2015 | 8 | 3 |
11/13/2015 | 54 | 4 |
You can make a straight table with the Weekend as dimension, all other columns are expressions. Instead of using the accumulation feature of the straight table use instead the expressions here to calculate the accumulation then the last column is the ratio of the preceding two expressed as a percentage.
WeekEnd | Module Assigned | Completed | RangeSum(Above([Module Assigned],0,RowNo())) | RangeSum(Above(Completed,0,RowNo())) | RangeSum(Above(Completed,0,RowNo())) / RangeSum(Above([Module Assigned],0,RowNo())) |
---|---|---|---|---|---|
6/19/2015 | 3 | 1 | 3 | 1 | 33.33% |
6/26/2015 | 271 | 113 | 274 | 114 | 41.61% |
9/25/2015 | 6 | 5 | 280 | 119 | 42.50% |
10/30/2015 | 2 | 1 | 282 | 120 | 42.55% |
11/06/2015 | 8 | 3 | 290 | 123 | 42.41% |
11/13/2015 | 54 | 4 | 344 | 127 | 36.92% |
hope this helps!