Qlik Community

Qlik DataMarket Discussions

Discussion Board for collaboration regarding Qlik DataMarket.

ak00428233
New Contributor III

To find out the growth rate of products sold for previous month or previous year

Hi ,

I have been facing difficulty in the expression to display the growth rate of products sold in previous month or previous year.

I have written the expression to find out the growth rate as (Sum([Units Sold])-Above(Sum([Units Sold])))/Above(Sum([Units Sold])).

This was displaying the growth rate but if i have data for 3 years namely 2013,2014,2015.The expression will give correct result for 2013.

But if i want to consider the sales of 2014 it should display the january month growth rate also comparing it to the last year december sales.

I have written expression for it as

=if(Year>=2014 and Month='Jan',Num((Sum([Units Sold])-sum({<Month={'Dec'},Year={'$vLastYear'}>}[Units Sold]))/sum({<Month={'Dec'},Year={$vLastYear}>}[Units Sold]),'#0.%'),

Num((Sum([Units Sold])-Above(Sum([Units Sold])))/Above(Sum([Units Sold])),'#0.%')).But i could not get my required solution.

Could anyone suggest me any other expression for it or the wrong written in my expression.

Please help. Appreciate your time and help in this.

Thank you.

1 Solution

Accepted Solutions
MVP
MVP

Re: To find out the growth rate of products sold for  previous month or previous year

Sure, that's why I mentioned, 'if you show all years'. If your years and month field load order is chronologic, you can try

=Only( Aggr( Sum({<Year, Month>} [Units Sold]) / Above(TOTAL Sum({<Year, Month>} [Units Sold])  -1, Year, Month))

Year and Month being your year and month field dimensions (Assuming only these two dimensions.

edit: added set analysis

13 Replies
MVP
MVP

Re: To find out the growth rate of products sold for previous month or previous year

May be something like this:

=If(Num(Month)=1,

Num((Sum([Units Sold]) - Below(Sum([Units Sold]), 11))/Below(Sum([Units Sold]), 11),'#0.%'),

Num((Sum([Units Sold]) - Above(Sum([Units Sold])))/Above(Sum([Units Sold])),'#0.%'))

Not entirely sure if you need 11 or 12, but I have a feeling it should be 11. If 11 doesn't work, try 12 as well.

MVP
MVP

Re: To find out the growth rate of products sold for previous month or previous year

Look into the concept of an As-Of table to get values for previous months.

The As-Of Table

ak00428233
New Contributor III

Re: To find out the growth rate of products sold for  previous month or previous year

Hi Sunny,

It was displaying the sales in jan for 2014 and 2015.

But it was displaying the sales in jan for 2013 also.Is it that according to the expression it was showing the jan sales and calculating the growth rate for previous month consequently.Is my understanding correct only?

MVP
MVP

Re: To find out the growth rate of products sold for previous month or previous year

And if you show all years, using Year and Month as dimensions in you chart, you can try just adding the total qualifier to overcome the column segment resetting.

=Sum([Units Sold]) / Above(TOTAL Sum([Units Sold])  -1

ak00428233
New Contributor III

Re: To find out the growth rate of products sold for  previous month or previous year

Its displaying only from feb swuehl if i select the year 2014.

ak00428233
New Contributor III

Re: To find out the growth rate of products sold for  previous month or previous year

Thank you everyone it is coming by modifying the expression as

=If(Num(Month)=1,

Num((Sum([Units Sold]) - Below(Sum([Units Sold]), 11))/Below(Sum([Units Sold]), 11),'#0.%'),

Num((Sum([Units Sold]) - Above(Sum([Units Sold]),1))/Above(Sum([Units Sold]),1),'#0.%'))

MVP
MVP

Re: To find out the growth rate of products sold for  previous month or previous year

I am not sure what you just said. Can you may be go over it one more time

ak00428233
New Contributor III

Re: To find out the growth rate of products sold for  previous month or previous year

Yaa Sunny I have entered the same expressio you told by modifying it in the last line as Num((Sum([Units Sold]) - Above(Sum([Units Sold]),1))/Above(Sum([Units Sold]),1),'#0.%')).

MVP
MVP

Re: To find out the growth rate of products sold for  previous month or previous year

Sure, that's why I mentioned, 'if you show all years'. If your years and month field load order is chronologic, you can try

=Only( Aggr( Sum({<Year, Month>} [Units Sold]) / Above(TOTAL Sum({<Year, Month>} [Units Sold])  -1, Year, Month))

Year and Month being your year and month field dimensions (Assuming only these two dimensions.

edit: added set analysis

Community Browser