topic Re: How can I calculate the growth rate using two dimension values? in App Development

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

JIEUNLIM — Sat, 16 Nov 2024 18:53:23 GMT

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?

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

lorenzoconforti — Wed, 25 Mar 2020 08:14:15 GMT

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

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

JIEUNLIM — Wed, 25 Mar 2020 09:15:56 GMT

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.

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

lorenzoconforti — Wed, 25 Mar 2020 09:31:38 GMT

Can you post the qvf file?

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

JIEUNLIM — Wed, 25 Mar 2020 10:56:59 GMT

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.

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

lorenzoconforti — Wed, 25 Mar 2020 11:01:19 GMT

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

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

JIEUNLIM — Thu, 26 Mar 2020 02:45:02 GMT

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.

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

lorenzoconforti — Thu, 26 Mar 2020 13:49:19 GMT

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

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

JIEUNLIM — Thu, 26 Mar 2020 14:09:00 GMT

Thank you So much!!!!

It is very helpful for me! 🙂

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

lorenzoconforti — Thu, 26 Mar 2020 14:12:49 GMT

no problem