topic Re: Growth Rate calculation in negative figures in QlikView

Growth Rate calculation in negative figures

upaliwije — Sat, 06 Oct 2018 06:26:40 GMT

Hi Friends

I have two negative figures in my data table for two years as shown below, I want to calculate the growth rate over previous years and I have written following expression.

2018 2017 Growth

-127,252

-154,523

((sum({$<YEAR={'2018'}>}[Nos])/

fabs(sum({$<YEAR={'2017'}>}[Nos])))-1)*100

Results -182.4% But actually the growth rate should be 18%.

Pls help me to correct my formula to get correct results

Re: Growth Rate calculation in negative figures

MarcoWedel — Sat, 06 Oct 2018 21:04:19 GMT

maybe something like:

Num(Sum({$<YEAR={'2018'}>}[Nos])/Sum({$<YEAR={'2017'}>}[Nos])-1,'0%')

hope this helps

regards

Marco

Re: Growth Rate calculation in negative figures

captain89 — Sun, 07 Oct 2018 10:34:08 GMT

Hi,

You should do the difference between 2017 and 2018 and then divide to the total of 2017.

In this case with a negative base you need to use fabs to get the absolute value.

You're interested in the positive sign between 2017 and 2018 that is +18%

(sum({$<Year={'2018'}>} Nos)-sum({$<Year={'2017'}>} Nos))/

fabs(sum({$<Year={'2017'}>} Nos))

then flag show value in % in format

hope this helps

Matteo

Re: Growth Rate calculation in negative figures

upaliwije — Sun, 07 Oct 2018 11:45:01 GMT

Dear Friends,

Pls help me to get my formula corrected

Re: Growth Rate calculation in negative figures

Or — Sun, 07 Oct 2018 11:52:37 GMT

It looks like your question has been correctly answered above. If you feel these answers are not correct, you're going to have to elaborate...

Re: Growth Rate calculation in negative figures

upaliwije — Sun, 07 Oct 2018 11:55:55 GMT

Thanks Macro,

Still the results is the same.No change.

Re: Growth Rate calculation in negative figures

upaliwije — Sun, 07 Oct 2018 12:00:52 GMT

When I apply your expression the result is the same.

Re: Growth Rate calculation in negative figures

captain89 — Sun, 07 Oct 2018 13:17:25 GMT

I think that you did a pivot table. And the year in this case is a dimension.

Try using another approach.

Create two variables: vYear= max(Year) and vYear_1 = max(Year)-1

Then you make a table with 3 expressions:

sum({$<Year={'$(vYear)'}>} Nos) and in the label put =$(vYear)

sum({$<Year={'$(vYear_1)'}>} Nos) and in the label put =$(vYear_1)

(sum({$<Year={'$(vYear)'}>} Nos)-sum({$<Year={'$(vYear_1)'}>} Nos))/ fabs(sum({$<Year={'$(vYear_1)'}>} Nos))

so if you select 2018 in the dimension year you get 2018, 2017 and the growth

hope this helps

Re: Growth Rate calculation in negative figures

MarcoWedel — Sun, 07 Oct 2018 22:48:55 GMT

Seems to work for me:

Can you post a sample application where you're getting a different result?

thanks

regards

Marco

Re: Growth Rate calculation in negative figures

upaliwije — Mon, 08 Oct 2018 04:09:04 GMT

Thanks Macro

It works that way. But the It should be positive percentage since the 2018 Negative figure is less than the negative figure of 2017. Am I correct ?

Re: Growth Rate calculation in negative figures

captain89 — Mon, 08 Oct 2018 09:16:18 GMT

That's impossible.

I tried and the result is right.

Is there a problem in your data model?

please see attachment

Re: Growth Rate calculation in negative figures

MarcoWedel — Mon, 08 Oct 2018 09:41:33 GMT

It's a negative percentage of a negative value resulting in a positive difference. So you have an alternative definition?

Re: Growth Rate calculation in negative figures

Or — Mon, 08 Oct 2018 11:14:17 GMT

The Wall Street Journal has an old guide to earnings reports that says this:

Net Income percent change is the change from the same period from a year ago. Percent change is not provided if either the latest period or the year-ago period contains a net loss [negative number]. On the digest page, if a company posts a profit in the latest period against a loss in the year-ago period, the percent change is represented as a “P”. Similarly, if a company posts a loss in the latest period against a profit in the year-ago period, the percent change is represented as a “L”.

If you insist on calculating this - and you shouldn't, in my opinion - you can write an IF() statement to check which figure is bigger. If 2017 is smaller (that is, more negative) and both values are negative, you can multiply the result by -1 to get "positive growth". This is, however, not really correct... there is no positive growth here, merely reduction in losses.