topic Re: 4x4 week growth in line graph in QlikView

4x4 week growth in line graph

padmanabhan_ram — Tue, 08 Dec 2015 09:13:36 GMT

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

Re: 4x4 week growth in line graph

MayilVahanan — Tue, 08 Dec 2015 09:25:18 GMT

Try like this

weekSample	Volume	=RangeSum(Above(Sum(volume),0, 4))	=RangeSum(Above(Sum(volume),4, 4))	=(Column(2) - Column(3)) / Column(3)
	550	0	0
1	10	10	0
2	20	30	0
3	30	60	0
4	40	100	0
5	50	140	10	13.0
6	60	180	30	5.0
7	70	220	60	2.7
8	80	260	100	1.6
9	90	300	140	1.1
10	100	340	180	0.9

Re: 4x4 week growth in line graph

padmanabhan_ram — Tue, 08 Dec 2015 09:40:23 GMT

Hi Mayil,

Thanks for your response, but I want it for the data that has been attached and in a line graph not as a table.

Regards,

Padmanabhan

Re: 4x4 week growth in line graph

MayilVahanan — Tue, 08 Dec 2015 09:55:10 GMT

Hi,

Thats for your reference.

You can change it to line chart.

Could you check the output is matched as per your expectation.

WeekEndingDate	=(RangeSum(Above(Sum(SumOfVolume),0, 4)) - RangeSum(Above(Sum(SumOfVolume),4, 4))) / RangeSum(Above(Sum(SumOfVolume),4, 4))

05/12/2014	3.16
12/12/2014	1.06
19/12/2014	0.36
26/12/2014	0.04
02/01/2015	-0.04
09/01/2015	-0.04
16/01/2015	-0.03
23/01/2015	0.09
30/01/2015	0.21
06/02/2015	0.23
13/02/2015	0.25
20/02/2015	0.16
27/02/2015	0.12
06/03/2015	0.11
13/03/2015	0.10
20/03/2015	0.11
27/03/2015	0.11
03/04/2015	0.10
10/04/2015	0.08
17/04/2015	0.06
24/04/2015	0.05
01/05/2015	0.04
08/05/2015	0.05
15/05/2015	0.04
22/05/2015	0.04
29/05/2015	0.00
05/06/2015	-0.01
12/06/2015	-0.01
19/06/2015	-0.02
26/06/2015	0.03
03/07/2015	0.01
10/07/2015	0.00
17/07/2015	0.01
24/07/2015	-0.02
31/07/2015	-0.01
07/08/2015	-0.00
14/08/2015	-0.01
21/08/2015	-0.01
28/08/2015	-0.01
04/09/2015	-0.00
11/09/2015	-0.02
18/09/2015	-0.02
25/09/2015	-0.03
02/10/2015	-0.05
09/10/2015	-0.01
16/10/2015	-0.02
23/10/2015	-0.01
30/10/2015	-0.01

Re: 4x4 week growth in line graph

padmanabhan_ram — Tue, 08 Dec 2015 09:59:32 GMT

Hi Mayil,

can you please make it for Product wise, because product isn't available in your output.

Regards,

Padmanabhan

Re: 4x4 week growth in line graph

MayilVahanan — Tue, 08 Dec 2015 10:07:11 GMT

Hi,

Could you please provide your expected result ?

Re: 4x4 week growth in line graph

padmanabhan_ram — Tue, 08 Dec 2015 10:18:26 GMT

Hi Mayil,

The expected result should be in the line graph, WeekEndingDate(Primary) and Product(secondary) as Dimension then may be the expression which you have shared. should give me the values for each week for respective products.

Br,

Padmanabhan

Re: 4x4 week growth in line graph

MayilVahanan — Tue, 08 Dec 2015 10:27:35 GMT

PFA

Re: 4x4 week growth in line graph

padmanabhan_ram — Tue, 08 Dec 2015 10:49:31 GMT

Hi Mayil,

Thanks for the attached. The QVW shows exactly the required value % when I select individual product explicitly but when none of the Product is selected then it shows different value. I am applying filters on Region = East.

expected output for few weeks is below.

	8/14	8/21	8/28	9/4	9/11	9/18	9/25	10/2	10/9	10/16	10/23	10/30
S	37.6%	30.8%	25.5%	22.6%	17.2%	17.0%	16.4%	13.4%	13.5%	13.1%	11.7%	10.1%
B	-5.5%	-3.7%	-4.8%	-4.8%	-5.4%	-6.0%	-6.8%	-8.3%	-5.0%	-4.3%	-2.3%	-2.5%
C	2.5%	2.3%	0.1%	-0.5%	-3.2%	-1.8%	-1.2%	-2.0%	1.2%	-0.9%	-1.1%	-0.8%
Q	-2.1%	-3.7%	-5.2%	-4.6%	-4.3%	-2.0%	-0.4%	-2.7%	2.0%	0.2%	-0.6%	1.3%

Re: 4x4 week growth in line graph

padmanabhan_ram — Tue, 08 Dec 2015 11:06:36 GMT

Hi Mayil,

I got it, we need to aggregate by Product, WeekEndingDate.

thank you for your quick responses.

Padmanabhan

Re: 4x4 week growth in line graph

padmanabhan_ram — Wed, 09 Dec 2015 04:40:34 GMT

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

Re: 4x4 week growth in line graph

MayilVahanan — Wed, 09 Dec 2015 05:37:01 GMT

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

Re: 4x4 week growth in line graph

padmanabhan_ram — Wed, 09 Dec 2015 05:40:24 GMT

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

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

Re: 4x4 week growth in line graph

arasantorule — Wed, 09 Dec 2015 05:48:57 GMT

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.

Re: 4x4 week growth in line graph

padmanabhan_ram — Wed, 09 Dec 2015 06:16:10 GMT

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

Thanks,

Padmanabhan

Re: 4x4 week growth in line graph

padmanabhan_ram — Wed, 09 Dec 2015 06:17:09 GMT

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

Br,

Padmanabhan

Re: 4x4 week growth in line graph

MayilVahanan — Wed, 09 Dec 2015 06:17:36 GMT

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

Re: 4x4 week growth in line graph

padmanabhan_ram — Wed, 09 Dec 2015 14:28:02 GMT

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