How can I calculate the growth rate using two dimension values?

[JIEUNLIM]

I want to calculate the growth rate.
'(Sum ([sales]) / ABOVE (Sum ([sales]))-1) * Avg (1)' was used as a formula for the growth rate.
The growth rate by year is correct.

At this time, company information is added to the dimension.

In this case, the growth rate of sales by year / company can be obtained from the line chart.

However, if a company is selected from the dimension, it is not displayed one by one.
For example, if you select 3 dimensions (company), only 2 companies are represented.
I think it's because of the ABOVE function or (-1) .

I have 2 dimensions and one measure. 

- dimension: year, company
- measure: sales

and, I would like to use Line chart. 

Is there any workaround?

[lorenzoconforti · Accepted solution]

It's a bit hard to follow

I think it would be better to create a sample dashboard to show your issue

I would be much easier to help you

[JIEUNLIM]

I attached an example file and additional comments.
I want to express sales growth by year and company.

Chart # 1:
Dimension: Year
Measure: Growth rate : (Sum ((Sales)) / ABOVE (Sum ([Sales]))-1) * Average (1)
** It is correct. 

Chart # 2:
Dimension 1: year
Dimension 2: Company
Measure: Growth rate : (Sum ((Sales)) / ABOVE (Sum ([Sales]))-1) * Average (1)

Chart # 3:
Dimension 1: year
Dimension 2: Company
Measure: Growth rate : (Sum ((Sales)) / ABOVE (Sum ([Sales]))-1) * Average (1)

** Selected 4 companies from line chart(# 2).
You can see this in the legend of the line chart.
However, this line was marked with only three companies in #3 chart.
Is this the reason for the 'Above function'?

I want to know the growth rate formula that can represent all four companies.
The growth rate formula is ((Sales this year)-(Sales last year) / (Sales last year)) * 100.

[lorenzoconforti · Accepted solution]

Can you post the qvf file?

[JIEUNLIM]

I use Qlik Sense, not Qlik view. 

Is it possible to create qvw file using Qlik sense?

If is it possible, could you let me know creating qvw file? I will post qvw file.

[lorenzoconforti · Accepted solution]

Qlik Sense uses QVF files; are you developing on Qlik Sense desktop? You will find the files under documents>Qlik>Sense>Apps

[JIEUNLIM]

I enclosed a file. 

This file is different from my screenshot.

Because, the screenshot was my company's private information. 

Anyway, the file I attached has same problem. 

When i choose 4 companies, the line chart shows only 3 companies. 

But legend information show 4 companies.

[lorenzoconforti · Accepted solution]

Use this as your measure:

=sum(aggr((Sum ([sales]) / ABOVE (Sum ([sales]))-1) * Avg (1), company, year))

Please note that year 2013 and 2019 have huge growth so they flatten the rest of the chart

[JIEUNLIM]

Thank you So much!!!!

It is very helpful for me! 🙂 

[lorenzoconforti · Accepted solution]

no problem