Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
New Contributor II

Progressive CPMU

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. 

 

 BCDEF 
  No of ComlInvoced QtyCPMUCPMU progressiveCPMU Progresivve - formula
4Week1515000033.3333.33333333C4*1000000/D4
5Week2819000042.1138.23529412(C4+C5)*1000000/(D4+D5)
6Week31017500057.1444.66019417(C4+C5+C6)*1000000/(D4+D5+D6)
7Week41221000057.1448.27586207(C4+C5+C6+C7)*1000000/(D4+D5+D6+D7)
8Week5416000025.0044.06779661(C4+C5+C6+C7+C8)*1000000/(D4+D5+D6+D7+D8)

 

CPMU = No of Complaints*1000000/Invoiced_Qty

 

Best Regards

Thanks

 Michal

1 Solution

Accepted Solutions
Highlighted
New Contributor II

Re: Progressive CPMU

 

Hi All,

 

I have found solution, for your record, pelase see below:

=((RangeSum(Above((SUM(Complaint)), 0, RowNo())))*1000000)/(SUM( Aggr( RangeSum(above (Sum(Invoiced_Qty)*Conversion_Factor,0,RowNo())),Product,WeekSUNSAT)))

 

It is working and I get expected results. 

 

BR

Michal

View solution in original post

1 Reply
Highlighted
New Contributor II

Re: Progressive CPMU

 

Hi All,

 

I have found solution, for your record, pelase see below:

=((RangeSum(Above((SUM(Complaint)), 0, RowNo())))*1000000)/(SUM( Aggr( RangeSum(above (Sum(Invoiced_Qty)*Conversion_Factor,0,RowNo())),Product,WeekSUNSAT)))

 

It is working and I get expected results. 

 

BR

Michal

View solution in original post