Growth Rate Calculation

[asknyldz]

Hi there,

I can't the calculate Growth Rate, where is the my fault? Here is the contents,

YEAR	=SUM(AMOUNT)	=(Sum(AMOUNT)/Before(Sum({<YEAR>}AMOUNT)) - 1) * Avg(1)
2013	300	-
2014	400	-
2015	500	-
2016	600	-
2017	700	-
2018	800	-

Thanks

[kruppas78]

Hi,

thats how i would do it:

in Script:

Table:
LOAD * INLINE [
YEAR, AMOUNT
2013, 300
2014, 400
2015, 500
2016, 600
2017, 700
2018, 800
];

DimYEAR:
LOAD Distinct CHR(39)&CONCAT(YEAR,CHR(39)&','&CHR(39))&CHR(39) AS DimYEAR
RESIDENT Table
ORDER BY YEAR;

LET vDimYEAR = Peek('DimYEAR');

DROP TABLE DimYEAR;

zjzhjfdsgdfgd

Dimension:

IF(ValueList($(vDimYEAR))=MAX(YEAR),SUM({<YEAR={'$(=MAX(YEAR))'}>}AMOUNT)/SUM({<YEAR={'$(=MAX(YEAR-1))'}>}AMOUNT)-1,
IF(ValueList($(vDimYEAR))=MAX(YEAR-1),SUM({<YEAR={'$(=MAX(YEAR-1))'}>}AMOUNT)/SUM({<YEAR={'$(=MAX(YEAR-2))'}>}AMOUNT)-1,
IF(ValueList($(vDimYEAR))=MAX(YEAR-2),SUM({<YEAR={'$(=MAX(YEAR-2))'}>}AMOUNT)/SUM({<YEAR={'$(=MAX(YEAR-3))'}>}AMOUNT)-1,
IF(ValueList($(vDimYEAR))=MAX(YEAR-3),SUM({<YEAR={'$(=MAX(YEAR-3))'}>}AMOUNT)/SUM({<YEAR={'$(=MAX(YEAR-4))'}>}AMOUNT)-1,
IF(ValueList($(vDimYEAR))=MAX(YEAR-4),SUM({<YEAR={'$(=MAX(YEAR-4))'}>}AMOUNT)/SUM({<YEAR={'$(=MAX(YEAR-5))'}>}AMOUNT)-1,
IF(ValueList($(vDimYEAR))=MAX(YEAR-5),SUM({<YEAR={'$(=MAX(YEAR-5))'}>}AMOUNT)/SUM({<YEAR={'$(=MAX(YEAR-6))'}>}AMOUNT)-1
))))))

Pro: Next year will be added without any change and can be sorted by Value

Con: hard coded the amount of years. Here max 6 Years, but can be extended.

Better Solution would be interesting, thanks!

[sunny_talwar · Accepted solution]

Before is a pivot table function... try above or below

=(Sum(AMOUNT)/Above(Sum({<YEAR>}AMOUNT)) - 1) * Avg(1)