Skip to main content
Announcements
Introducing Qlik Answers: A plug-and-play, Generative AI powered RAG solution. READ ALL ABOUT IT!
cancel
Showing results for
Search instead for
Did you mean:
Contributor III

## Growth Rate Calculation

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)
2013300-
2014400-
2015500-

2016

600-
2017700-
2018800-

Thanks

1 Solution

Accepted Solutions
MVP

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

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

2 Replies
Contributor III

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!

MVP

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

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

Community Browser