Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
JIEUNLIM
Contributor II
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 (3)
5 Solutions

Accepted Solutions
lorenzoconforti
Specialist II
Specialist II

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

lorenzoconforti
Specialist II
Specialist II

Can you post the qvf file?

View solution in original post

lorenzoconforti
Specialist II
Specialist II

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

lorenzoconforti
Specialist II
Specialist II

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

9 Replies
lorenzoconforti
Specialist II
Specialist II

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
Contributor II
Contributor II
Author

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
Specialist II
Specialist II

Can you post the qvf file?

JIEUNLIM
Contributor II
Contributor II
Author

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
Specialist II
Specialist II

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

JIEUNLIM
Contributor II
Contributor II
Author

 

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
Specialist II
Specialist II

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
Contributor II
Contributor II
Author

Thank you So much!!!!

It is very helpful for me! 🙂 

lorenzoconforti
Specialist II
Specialist II

no problem