topic Re: How to calculate the Growth in QlikView

How to calculate the Growth

Mon, 19 Jan 2015 04:51:30 GMT

Dear All,

I here by attached my sample data, which i want to calculate the growth. can any one help me in this regard.

Rgds,

Priyantha.

Re: How to calculate the Growth

anbu1984 — Mon, 19 Jan 2015 05:04:56 GMT

Can you post expected output

Re: How to calculate the Growth

Mon, 19 Jan 2015 05:49:45 GMT

growth can be represented by Line chart or bar chart as required by user on the basis of different Dimension,

if you dont have clear requirement then show them all possible representation by grouping dimension in drill down or cyclic group and show both chart line and bar with the help of fast change option as in Image below or find a sample application in attachment

Re: How to calculate the Growth

koushik_btech20 — Mon, 19 Jan 2015 06:37:21 GMT

You have post your desired output or the formula for calculating the growth.

Re: How to calculate the Growth

Thu, 22 Jan 2015 09:34:44 GMT

Dear Anbu,

My table like this,

PRD_CODE	BCOD	MONTH	YEAR	PTPR	GROWTH
1A	B100	APR	2012	178740
1B	B100	APR	2012	214184
1C	B100	APR	2012	205433

1A	BB00	APR	2013	48813
1B	BB00	APR	2013	76630
1C	BB00	APR	2013	-36508

1A	BB00	APR	2014	128540
1B	BB00	APR	2014	4402
1C	BB00	APR	2014	51992

I want to calculate the Growth of 2013 against the results of 2012. and 2014 growth against the year 2013.

What is the formula i should applied.

Simple example for 2014 1A product, = (128540-48813)/48813

Please help,

Rgds,

Priyantha.

Re: How to calculate the Growth

anbu1984 — Thu, 22 Jan 2015 09:52:15 GMT

Temp:
Load PRD_CODE,BCOD,MONTH,YEAR,PTPR From Excel.xlsx

Final:
NoConcatenate
Load *,If(PRD_CODE=Previous(PRD_CODE) And BCOD = Previous(BCOD) And MONTH = Previous(MONTH) And
Year = Previous(Year)+1, (PTPR - Previous(PTPR))/Previous(PTPR)) As Growth Resident Temp Order by PRD_CODE,BCOD,MONTH,YEAR;

Drop table Temp;

Re: How to calculate the Growth

Thu, 22 Jan 2015 11:23:11 GMT

Hi Priyantha,

You can achieve the desired output by the following steps:

1) create a grid chart.

2) Add BCOD,PROD_CODE as dimension

3) In expressions,use the following set analysis:

((sum({<Year ={"$(=max(Year))"}>}PTPR) - sum({<Year ={"$(=max(Year)-1)"}>}PTPR))/sum({<Year ={"$(=max(Year)-1)"}>}PTPR) )

and label it as growth

Output is for growth of 2014 against 2013:

PROD_CODE Growth

1A 1.633

1B -0.94

1C -1.14

The above output suffices the formula for 2014 1A product :(128540-48813)/48813 = 1.633

By default, the values displayed are for current year.On selection of any other year,the corresponding values would change according to the selected year and its previous one.

Hope this information is useful.

regards,

Snehal Nabar

Re: How to calculate the Growth

Fri, 23 Jan 2015 03:32:28 GMT

Dear Snehal,

Thanks a lot for the valuable reply,

In this case we want to consider the 'MONTH' also not only the year.

My attempt to calculate the growth against the previous year same month and same product.

Please help me to find the way...

Rgds,

Priyantha

Re: How to calculate the Growth

Fri, 23 Jan 2015 03:35:33 GMT

Dear Anbu,

Thanks for the early reply,

Your scrip change was tried and failed. Please give some solution with expression part in the table.

Rgds,

Priyantha.

Re: How to calculate the Growth

krishna20 — Fri, 23 Jan 2015 08:27:22 GMT

Hi,

Try like this,Please concentrate at the brackets

=if(PROD_CODE='1A' or if(PROD_CODE='1B' or if(PROD_CODE='1C' , (sum({<year={"$(=max(year))"}>}PTPR)-sum({<year={"$(=max(year)-1)"}>}PTPR)) /

sum({<year={"$(=max(year)-1)"}>}PTPR)))))

Regards

Krishna

Re: How to calculate the Growth

Fri, 23 Jan 2015 09:27:15 GMT

Hi Krishna,

Thanks for early reply,

As this is sample data i have mentioned only 03 products. in my actual data base i have more more than 100 product codes. Therefore it's impossible to use your suggestion .

Rgds.

Priyantha.

Re: How to calculate the Growth

Wed, 28 Jan 2015 03:05:18 GMT

Dear Gupta,

My table like this,

PRD_CODE	BCOD	MONTH	YEAR	PTPR	GROWTH
1A	B100	APR	2012	178740
1B	B100	APR	2012	214184
1C	B100	APR	2012	205433

1A	BB00	APR	2013	48813
1B	BB00	APR	2013	76630
1C	BB00	APR	2013	-36508

1A	BB00	APR	2014	128540
1B	BB00	APR	2014	4402
1C	BB00	APR	2014	51992

I want to calculate the Growth of 2013 against the results of 2012. and 2014 growth against the year 2013.

What is the formula i should applied.

Simple example for 2014 1A product, = (128540-48813)/48813

Please help,

Rgds,

Priyantha.

Re: How to calculate the Growth

Wed, 28 Jan 2015 03:12:19 GMT

Dear Snehal,

Your Expression status is OK, But something the is some thing wrong and expression result shows zero.

See the following table.

BCOD	MO_NM	CLA_CODE	NEW	REN	ADD	REFUND	TOTAL	NO.POL	BUDGET	ACH%	LAST_YEAR	GRO%
BA00	MOTOR	M3	59316	79321	0	0	138637	259	633500	22%	0	0.00%
		M4	96949	55605	0	0	152554	24	45250	337%	0	0.00%
		MC	1943244	3603725	20256	1744390	3822835	122	3846250	99%	0	0.00%
		Total	2099509	3738651	20256	1744390	4114026	405	4525000	91%	0	0.00%
	NON_MOTOR	EN	157200	77900	0	49200	185900	2	150220	124%	0	0.00%
		FR	128103	475473	27900	138890	492586	11	450660	109%	0	0.00%
		HH	6220	138360	0	24200	120380	6	85840	140%	0	0.00%
		MR	0	0	0	0	0	0	21460	0%	0
		MS	341583	242100	0	92660	491023	15	536500	92%	0	0.00%
		PP	9547	19790	0	0	29337	47	236060	12%	0	0.00%
		SS	0	133320	0	11565	121755	1	128760	95%	0	0.00%
		TC	38841	351887	5345	179760	216313	23	493580	44%	0	0.00%
		TT	0	0	0	0	0	0	42920	0%	0
		Total	681494	1438830	33245	496275	1657294	105	2146000	77%	0	0.00%
	Total		2781003	5177481	53501	2240665	5771320	510	6671000	87%	0	0.00%

Re: How to calculate the Growth

jagan — Wed, 28 Jan 2015 06:08:45 GMT

Hi,

Try like this in script

Temp:

LOAD * INLINE [

PRD_CODE, BCOD, MONTH, YEAR, PTPR

1A, B100, FEB, 2012, 151253

1B, B100, FEB, 2012, 142792

1C, B100, FEB, 2012, 328884

1A, B100, JAN, 2012, 130269

1B, B100, JAN, 2012, 314794

1C, B100, JAN, 2012, 307519

1A, B100, MAR, 2012, 204227

1B, B100, MAR, 2012, 259359

1C, B100, MAR, 2012, 369557

1A, BB00, FEB, 2013, 404408

1B, BB00, FEB, 2013, 108903

1C, BB00, FEB, 2013, 79301

1A, BB00, JAN, 2013, -17120

1B, BB00, JAN, 2013, 214956

1C, BB00, JAN, 2013, 270775

1A, BB00, MAR, 2013, -141303

1B, BB00, MAR, 2013, 144465

1C, BB00, MAR, 2013, 261256

1A, BB00, FEB, 2014, 227194

1B, BB00, FEB, 2014, 139685

1C, BB00, FEB, 2014, 156541

1A, BB00, JAN, 2014, 112225

1B, BB00, JAN, 2014, -24225

1C, BB00, JAN, 2014, 469485

1A, BB00, MAR, 2014, 259808

1B, BB00, MAR, 2014, 276639

1C, BB00, MAR, 2014, 4107

];

LOAD

If(PRD_CODE = Peek(PRD_CODE) AND MONTH = Peek(MONTH), Peek(PTPR), PTPR) AS PreviousValue

RESIDENT Temp

ORDER BY PRD_CODE, MONTH, YEAR;

DROP TABLE Temp;

Regards,

Jagan.

Re: How to calculate the Growth

Wed, 28 Jan 2015 06:49:11 GMT

Dear Jagan Mohan,

Thanks lot for the early reply.

As I have uploaded sample data only i have mentioned 03 'PRD_CODE's. In my database i have more than 100 'PRD_CODE's. So It's impossible to load all the 'PRD_CODE's in the script level.

My attempt is to find an expression to calculate the growth like this

Growth = [Current year same month PTPR - Last year same month PTPR]/Last year same month PTPR

So how can i do this calculation through an Expression.

Rgds,

Priyantha.

Re: How to calculate the Growth

jagan — Wed, 28 Jan 2015 07:35:52 GMT

Hi,

You can replace Inline script with your data, it works even if you have 1000s of products, the logic is same, I just given Inline for test purpose.

Temp:

LOAD *

FROM FileName;

Data:

LOAD

If(PRD_CODE = Peek(PRD_CODE) AND MONTH = Peek(MONTH), Peek(PTPR), PTPR) AS PreviousValue

RESIDENT Temp

ORDER BY PRD_CODE, MONTH, YEAR;

DROP TABLE Temp;

Now you can use

=(PTPR - PreviousValue)/PreviousValue

Hope this helps you.

Regards,

Jagan.

Re: How to calculate the Growth

SreeniJD — Wed, 28 Jan 2015 07:36:17 GMT

Hi Priyantha,

Please try to have three pre aggregated facts like Growth_cur, Growth_prev1, Growth_prev2 and calculate accordingly. this will simplify your calculation and will be robust for handling huge data.

Let me know if you need a script.

Thanks,

Sreeni

Re: How to calculate the Growth

Wed, 28 Jan 2015 07:52:10 GMT

Dear Jagan,

I have tried the way you introduced and observed that "previous value" in your scrip represents the same value as the Current PTPR. So something wrong in the script .

Please Help.

Rgds,

Priyantha.

Re: How to calculate the Growth

Wed, 28 Jan 2015 07:53:31 GMT

Dear Sreeni,

Thaks for the early reply,

Script Please.

Rgds,

Priyantha.

Re: How to calculate the Growth

Wed, 28 Jan 2015 08:22:21 GMT

Hi Priyantha,

Hope this will help u.