Hi All,
I'm currently working on complaints analyses. I need to calculate progressive CPMU(complaints indicator) . Could you please advise me how can I achieve it? please see below:
to calculate CPMY/week I am using below expression, which working with no issues, and giving me correct numbers
=(SUM(Complaint)*1000000)/(SUM(Aggr(SUM(Invoiced_Qty)*Conversion_Factor,Product,WeekSUNSAT)))
For Progressive CPMU i use below, which not giving me correct numbers:
=((RangeSum(Above(SUM(Complaint), 0, RowNo())))*1000000)/(RangeSum(Above(SUM(Aggr(SUM(Invoiced_Qty)*Conversion_Factor,Product,WeekSUNSAT))), 0, RowNo()))
Please see below sample what I am trying to achieve, from Excel. Basically, I am looking for two cumulative sum, however one of them is aggregated by two dimensions.
| | B | C | D | E | F | |
| | | No of Coml | Invoced Qty | CPMU | CPMU progressive | CPMU Progresivve - formula |
| 4 | Week1 | 5 | 150000 | 33.33 | 33.33333333 | C4*1000000/D4 |
| 5 | Week2 | 8 | 190000 | 42.11 | 38.23529412 | (C4+C5)*1000000/(D4+D5) |
| 6 | Week3 | 10 | 175000 | 57.14 | 44.66019417 | (C4+C5+C6)*1000000/(D4+D5+D6) |
| 7 | Week4 | 12 | 210000 | 57.14 | 48.27586207 | (C4+C5+C6+C7)*1000000/(D4+D5+D6+D7) |
| 8 | Week5 | 4 | 160000 | 25.00 | 44.06779661 | (C4+C5+C6+C7+C8)*1000000/(D4+D5+D6+D7+D8) |
CPMU = No of Complaints*1000000/Invoiced_Qty
Best Regards
Thanks
Michal
