How to calculate Sales Growth in pivot chart for last 12months?

[ShivangPurwar]

Hello All,

I am trying to calculate Sales Growth in pivot table for last 12months. But some how its give me all months in dimension and -1 value in the measure. 

My pivot table consist of Row:Country, Brand, Column : Month Year. 

As a measure, I am using this expression. 

(sum({<[Month Year]={">=$(=Date(AddMonths(Date($(vMaxDateExternal_Cash_New)),-11),'YYYY-MM-DD')) <=$(=Date($(vMaxDateExternal_Cash_New),'YYYY-MM-DD'))"}>}[Cash_Sales])-Above(Sum({<[Month Year]={">=$(=Date(addmonths(Date($(vMaxDateExternal_Cash_New)),-23))) <=$(=Date(addmonths(Date($(vMaxDateExternal_Cash_New)),-12)))"}>}[Cash_Sales]),0,12))
/Above(Sum({<[Month Year]={">=$(=Date(addmonths(Date($(vMaxDateExternal_Cash_New)),-23))) <=$(=Date(addmonths(Date($(vMaxDateExternal_Cash_New)),-12)))"}>}[Cash_Sales]),0,12)

vMaxDateExternal_Cash_New -> Will give me Mar 2022

Kindly please help, as I am stuck in this for a week.

[stevedark]

Hi @ShivangPurwar 

In each cell are you trying to show growth from the initial month (e.g. Apr 2020) or is it from the previous month?

If it is always the initial you will be wanting to use set analysis to pick the month and TOTAL to apply that across dimensions. Something like:

(sum(Value) - sum(TOTAL <Country_RE, Competitor> {<[Month Year]={"$(vMinMonthYear)"}>}Value)) / sum(TOTAL <Country_RE, Competitor> {<[Month Year]={"$(vMinMonthYear)"}>}Value)

The above function doesn't work so well when you have multiple dimensions.

If you are trying to get the movement from the previous month I would look at creating a Prior Month Compare dimension in the load script with all months in, and each month linking to the prior month also, with a version tag. So, for this month you would have:

Month	Prior Month Compare	Version
May-22	May-22	Current
Apr-22	May-22	Prior

That would then go back for all prior months also, and you would use the Prior Month Compare dimension in place of Month in the pivot. You then have access to current and prior months in the same cell, and can pick with set analysis:

(sum({<Version={'Current'}>}Value) - sum({<Version={'Prior'}>}Value) / sum({<Version={'Prior'}>}Value)

I explain this approach in detail in this blog post:

https://www.quickintelligence.co.uk/qlikview-accumulate-values/

Hope that helps,

Steve

[ShivangPurwar]

Hi @stevedark , Thank you for your reply,  Actually In Column level I will be having Month year for last 12Months, in which I am looking in MAT way, so foreg May 2022 is the month year, so for previous 12months from June 2021 - May 2022. Will be my Dimension(Column) and. In my calculation I have to calculate last 12months.So for that last 24months. 

Numerator : Sum of sales current period(June 2021- May 2022)/ Sum of sales previous period(June 2020- May 2021)-1

[stevedark]

Hi @ShivangPurwar 

There are two approaches to this, with different benefits and drawbacks.

One approach is actually outlined in the same blog post as above - where you have a table that joins each month to the twelve subsequent months - so you can have a rolling 12. This can make selections a bit weird.

The other approach (and this is probably the one I'd take) is a bit more time consuming, but more robust. This way you would have a table with twelve measures, one for each of the periods you are wanting. The date is not then a dimension at all, rather coded into each of the expressions.

The expression would be something like:

sum({<Month={">=$(=$(vMonthsBack(11)))<=$(=$(vMonthsBack(0)))"}>}Value) /
sum({<Month={">=$(=$(vMonthsBack(23)))<=$(=$(vMonthsBack(12)))"}>}Value)

The secret sauce in this is having a variable which takes a parameter. Once you have this things become quite a bit easier. I've described the technique in this blog post:

https://www.quickintelligence.co.uk/variables-parameters-load-script/

The variable vMonthsBack would be something like:

date(addmonths(monthstart(today()), -$1), 'MMM-YY')

The double $(=$( is required to make that syntax work, as you can't start a variable with a parameter with an equals (not sure why, it just doesn't work).

Obviously, you would have to have that expression in twelve different measures, which would be a bit of a pain, so you can wrap the expression in a variable with a parameter also, so something like vRolling12Variance with contents of:

sum({<Month={">=$(=$(vMonthsBack($1-11)))<=$(=$(vMonthsBack($1-0)))"}>}Value) /
sum({<Month={">=$(=$(vMonthsBack($1-23)))<=$(=$(vMonthsBack($1-12)))"}>}Value)

You can then just have twelve measures with vRolling12Variance(0), vRolling12Variance(1) all the way up to 11.

You will need to do some careful testing, and no doubt some debugging as I have just typed this into the message off the top of my head, but I think that should put you in the right direction.

Make sure the date format you have in the variable matches the format of the Month field in the data model.

Test the expression by having a table with Month as the dimension and change the division in the expression to a plus - this way you can call vRolling12Variance(0) and check it encapsulates the right 24 month period, then do the same for the other 11. Once it is working you can change it back to divide.

You may also want to take a look at this example on set analysis:
https://community.qlik.com/t5/Qlik-Sense-Documents/Qlik-Sense-App-Prior-Period-Comparison-with-Set-A...

Hope that all makes some kind of sense?

Cheers,

Steve

[ShivangPurwar · Accepted solution]

Hi @stevedark , This approach is really nice, and might be useful in another case scenario.  

I have come up with a solution, which is fulfilling my requirement.

Num((Sum({<"Month Year"={">=$(=Date(AddMonths(Date($(vMaxDateExternal_Cash)),-11),'YYYY-MM-DD')) <=$(=Date($(vMaxDateExternal_Cash),'YYYY-MM-DD'))"}>}[Cash_Sales])/
sum({<"Month Year"=>} aggr(Above((above(Sum({<[Month Year]=>}[Cash_Sales]),0,12))
,12),Country_RE,Country,Competitor,([Month Year],Text)))
)-1
,'##.#%')