Growth Rate calculation in negative figures

[upaliwije]

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

[MarcoWedel]

maybe something like:

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

?

hope this helps

regards

Marco

[captain89]

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

[upaliwije]

Dear Friends,

Pls help me to get my formula corrected

[Or]

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...

[upaliwije]

Thanks Macro,

Still the results is the same.No change.

[upaliwije]

When I apply your expression  the result is the same.

[captain89]

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

[MarcoWedel]

Seems to work for me:

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

thanks

regards

Marco

[upaliwije]

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 ?