Issue with total and aggregate function

padmanabhan_ram — Thu, 10 Dec 2015 05:25:07 GMT

Hi, @

I am trying to create a line graph for the Market share for 4x4 rolling week with the below sample data.

Primary Dimension as weekendingdate and secondary dimension as product. market share as expression.

WeekEndingDate	Product	Volume
11/09/2015	B	2500.514
11/09/2015	C	2535.499
11/09/2015	Q	2621.334
11/09/2015	S	786.003
18/09/2015	B	2613.032
18/09/2015	C	2879.875
18/09/2015	Q	3016.402
18/09/2015	S	838.874
25/09/2015	B	2537.9
25/09/2015	C	2718.5
25/09/2015	Q	2938.999
25/09/2015	S	878.431
02/10/2015	B	2682.554
02/10/2015	C	2785.829
02/10/2015	Q	2895.987
02/10/2015	S	923.699
09/10/2015	B	2564.971
09/10/2015	C	2607.318
09/10/2015	Q	2944.698
09/10/2015	S	860.106
16/10/2015	B	2561.301
16/10/2015	C	2797.65
16/10/2015	Q	2892.23
16/10/2015	S	965.714
23/10/2015	B	2480.962
23/10/2015	C	2658.131
23/10/2015	Q	2863.209
23/10/2015	S	959.583
30/10/2015	B	2464.132
30/10/2015	C	2776.401
30/10/2015	Q	2921.564
30/10/2015	S	991.447

for example the week 30/10/2015 the calculation for market share for S should be

Current Week Share:

sum(volume for week 30,23,16,9)=3776,

sum of total volume for the week 30,23,16,9 including all the product =36309.

Share=3776/36309=10.3

Previous Week Share:

sum(volume for previous week 2,25,18,11)=3427,

sum of total volume for the previous week 2,25,18,11 including all the product =36153.

Share=3427/36153=9.4

Share Change is 10.3-9.4=0.9 this is for product S, the same calculation should be considered for other products.

I am able to get the sum(volume for week 30,23,16,9) which is 'aggr(rangesum(above(total sum(SumOfVolume),0,4)),Product,WeekEndingDate)'

and

sum(volume for previous week 2,25,18,11) which is aggr(rangesum(above(TOTAL sum(SumOfVolume),4,4)),Product,WeekEndingDate)

but unable to make everything in a single expression.

Please help me to get the logic in QlikView.

Br,

Padmanabhan

Re: Issue with total and aggregate function

padmanabhan_ram — Fri, 11 Dec 2015 04:45:35 GMT

Hi,

I got the solution, we should be using the below expression to get the rolling 4x4 market share.

aggr(rangesum(above(TOTAL sum(SumOfVolume),0,4)),Product,WeekEndingDate)/aggr(rangesum( above(TOTAL sum(TOTAL <WeekEndingDate> SumOfVolume),0,4)),Product,WeekEndingDate))

(aggr(rangesum(above(TOTAL sum(SumOfVolume),4,4)),Product,WeekEndingDate)/aggr(rangesum( above(TOTAL sum(TOTAL <WeekEndingDate> SumOfVolume),4,4)),Product,WeekEndingDate))

Thanks,

Padmanabhan

topic Issue with total and aggregate function in QlikView

Issue with total and aggregate function

Re: Issue with total and aggregate function