Progressive CPMU

MS_UK — Tue, 29 Oct 2019 12:54:31 GMT

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

Re: Progressive CPMU

MS_UK — Fri, 01 Nov 2019 11:40:13 GMT

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.

Michal

topic Progressive CPMU in QlikView

Progressive CPMU

Re: Progressive CPMU