How do I calculate % Growth?

[#1]

Hi

I have been trying to calclate % growth of 2 expressions I have calculated but can't for the life of me figure it out.

I am using,

current year sales; sum({$<Year={$(=Only(Year))}>}value_inv-value_crd-excluded_value)

previous year sales; sum({$<Year={$(=Only(Year)-1)}>}value_inv-value_crd-excluded_value)

I have the sales growth (current year sales- previous year sales) but I cannot get % growth. I read that Aggr is a good way of getting the growth but I cannot work the calculation out. Can someone help?

Thanks in advance

[#2]

Hi,

try this

current year sales; sum({$<Year={$(=max(Year))}>}value_inv-value_crd-excluded_value)

previous year sales; sum({$<Year={$(=max(Year)-1)}>}value_inv-value_crd-excluded_value)

Now you can calculate (current year sales-Prev year sales)

and in number tab you can check that show %

[martinpohl · Accepted solution]

why using aggr?

isn't

(current year sales- previous year sales)/previous year sales

the answer?

Regards

[#4]

Hi

Rakesh, this only shows me the difference of the 2 numbers in % format. I need to show the % growth.

I was looking through posts similar to this and people were suggesting aggr as a solution to the % problem. I have tried the (current year expression-previous year expression)/previous year expression as this is the way I work out % growth in excel but it does not work either.

[jonathandienst]

Hi

If you are using this expression in a chart/table and the value of Only(Year) is different, then your expression will not work as the set expression is calculated once for the whole chart and not row by row. Otherwise, you need something like:

     sum({$<Year={$(=Max(Year))}>} value_inv-value_crd-excluded_value)

(and similar for the previous year)

HTH

Jonathan

[#6]

Thanks

I have now changed to Max. What expression do i need to work out the % growth of these 2 expressions?

[jonathandienst]

Hi

You can do a simple column calculation if you need it row-by-row. Lets assume that these two are expression 1 and expression 2. Then for the growth,

          (column(2) - column(1)) / column(1)

set the number option to % to see them as percentages

HTH

Jonathan

[#8]

I have done this but it still does not seem to work. I've done (ColumnNo(5)-ColumnNo(6))/ColumnNo(6) and marked as percentage. I've tried the names of the columns also (Qty of Current-Qty of previous)/Qty of previous. I've also typed the original forumla out in the same format; sum({$<Year={$(=Max(Year))}>} value_inv-value_crd-excluded_value)-sum({$<Year={$(=Max(Year)-1)}>} value_inv-value_crd-excluded_value)/sum({$<Year={$(=Max(Year))-1}>} value_inv-value_crd-excluded_value)

Apologies if I am missing something simple here. I am a new Qlik user

[#9]

I have eventually managed this. It was easy, I was just overlooking a simple issue and overthinking the formula. Thanks for the help.