Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

lysine0617
New Contributor II

How to calculate growth rate in Qlik Sense?

I have a pivot table in Qlik Sense as follow:

growth rate.PNG

Anybody knows how to calculate the growth rate by year base on this table?

Tags (1)
1 Solution

Accepted Solutions
lysine0617
New Contributor II

Re: How to calculate growth rate in Qlik Sense?

Hi Sunny,

Thanks a lot!

I change the function After() to before().

Using (Sum(Measure)/Before(Sum({<Year>}Measure)) - 1) * Avg(1) in expression.

It worked!

7 Replies
MVP
MVP

Re: How to calculate growth rate in Qlik Sense?

Something like this

(Sum(Measure)/After(Sum({<Year>}Measure)) - 1) * Avg(1)

hiteshpatil11
New Contributor III

Re: How to calculate growth rate in Qlik Sense?

Hi Yashu,


If I got your requirement correctly then you are trying to calculate YoY - Year Over Year growth from the table


For Example: 2017-18: 10782

                       2016-17: 10478

then Growth Rate for Year 2017-18 will be (10782/10478)-1 i.e. 2.90%.

For above problem statement, one solution which can be proposed is:

1. First Order your table on Year in Ascending Order while loading.

Example: Your table after loading script should be in below order:

SourceTable.JPG


2. Add one more Field while loading your table in Load Editor using Previous() Function, as shown below.

This field will carry your previous year Total concurrently with you current year field.

Load Editor.JPG


3. Basis above fields,

    Growth Rate Measure will be: =Sum(Sales)/Sum(LYSales)-1


4. Output Table:

GrowthRate.JPG


Please check if this works for you.


Regards,

Hitesh

lysine0617
New Contributor II

Re: How to calculate growth rate in Qlik Sense?

Hi Sunny,

Thanks a lot!

I change the function After() to before().

Using (Sum(Measure)/Before(Sum({<Year>}Measure)) - 1) * Avg(1) in expression.

It worked!

lysine0617
New Contributor II

Re: How to calculate growth rate in Qlik Sense?

Hi Hitesh,

I really appreciate your help!

The data not just include this two columns, and 'Sales' need to be summed up in order to get the total of sales. How should I define the Previous() in this case?

eg. Table include Year, Country, Category, Sales four columns.

Thanks again!

MVP
MVP

Re: How to calculate growth rate in Qlik Sense?

Awesome, please close the thread in that case

Best,

Sunny

zebhashmi
Valued Contributor

Re: How to calculate growth rate in Qlik Sense?

Please close it

hiteshpatil11
New Contributor III

Re: How to calculate growth rate in Qlik Sense?

hi yashu,

Previous() will work for calculating growth when you have Year dimension only, so that you can bring value of previous row value concurrently with your current row value.

In case of multiple dimensions, After() will be the solution.

Thanks,

Hitesh

Community Browser