4x4 week growth in line graph

[padmanabhan_ram]

Hi All,

Hope you are doing good!!!

I need your help to identify logic in QlikView for calculating 4x4 week volume growth and present in a line graph. I have attached the required data. The requirement is to have Week Ending Date as Primary dimension, Product as Secondary secondary.

In the expression I need a logic that can calculate and give the % of  growth for each week using 4x4 week rolling.

For Example,

week, volume

1,10

2,20

3,30

4,40

5,50

6,60

7,70

8,80

9,90

10,100

the expression calculation should give value for each week say week 8 should do sum(volume of week8,7,6,5) which is 260 - sum(volume of week 4,3,2,1) which is 100 and output should be (260-100)/100=1.6.

the value for week 9 should be (300-140)/140=1.1

the value for week 10 should be (340-180)/180=0.8

please let me know if you find the logic.

Thanks in advance.

Padmanabhan

[padmanabhan_ram]

Hi Mayil,

I am getting exact values for most of the weeks but for the initial three weeks it gives me negative values which seems to be wrong.I can understand that since it is rolling 4x4 weeks for the first three months the logic cannot be applied. Is there way where we can apply the logic for the rest of the weeks and for the first three weeks divide by it's previous weeks value.

Br,

Padmanabhan

[MayilVahanan]

HI

Can you provide your final expression which you are currently using?

[padmanabhan_ram]

aggr((RangeSum(Above(Total Sum(SumOfVolume),0, 4)) - RangeSum(Above(TOTAL Sum(SumOfVolume),4, 4))) /

RangeSum(Above(Total Sum(SumOfVolume),4, 4)),Product,WeekEndingDate)

[arasantorule]

Hi Padmanabhan,

You can include if(rowno()<4) and 3 and 2 in the expression and change the logic slightly for the same. Above expression will work for remaining rows

Hope this helps.

Thanks.

[padmanabhan_ram]

Thanks Arasan, I did alter the dimension and the same can be achieved as you said in the expression.

Thanks,

Padmanabhan

[padmanabhan_ram]

But this isn't the proper solution, if anyone gets better solution please let me know. This can used as a workaround.

Br,

Padmanabhan

[MayilVahanan]

Hi

Try some thing like this

if(Sum(Aggr(RangeSum(Above(TOTAL Sum(SumOfVolume),4, 4)), Product, WeekEndingDate)) =0, yourexpectedexpression,

Sum(aggr((RangeSum(Above(Total Sum(SumOfVolume),0, 4)) - RangeSum(Above(TOTAL Sum(SumOfVolume),4, 4))) /

RangeSum(Above(Total Sum(SumOfVolume),4, 4)),Product,WeekEndingDate)))

[padmanabhan_ram]

Hi Mayil,

I am trying to calculate the Market Share for the same data, instead of volume growth we have calculate the market share for each week at 4x4 rolling week.

the calculation would be,

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 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.

can you please help me if you can find the logic.

Br,

Padmanabhan