How to calculate the Growth

[#1]

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.

[#2]

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.

[#3]

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.

[#4]

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%

[jagan]

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.

[#6]

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.

[jagan]

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.

[SreeniJD]

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

[#9]

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. 

[#10]

Dear Sreeni,

Thaks for the early reply,

Script Please.

Rgds,

Priyantha.

[#11]

Hi Priyantha,

Hope this will help u.