Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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