Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
New Contributor II

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

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?

Labels (4)
5 Solutions

Accepted Solutions
Highlighted
Valued Contributor II

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

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

View solution in original post

Highlighted
Valued Contributor II

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

Can you post the qvf file?

View solution in original post

Highlighted
Valued Contributor II

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

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

View solution in original post

Highlighted
Valued Contributor II

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

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

View solution in original post

Highlighted
Valued Contributor II

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

9 Replies
Highlighted
Valued Contributor II

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

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

View solution in original post

Highlighted
New Contributor II

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

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.

Highlighted
Valued Contributor II

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

Can you post the qvf file?

View solution in original post

Highlighted
New Contributor II

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

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.

Highlighted
Valued Contributor II

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

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

View solution in original post

Highlighted
New Contributor II

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

 

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.

Highlighted
Valued Contributor II

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

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

View solution in original post

Highlighted
New Contributor II

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

Thank you So much!!!!

It is very helpful for me! 🙂 

Highlighted
Valued Contributor II

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