21 Replies Latest reply: Sep 28, 2015 5:59 PM by

# YoY and MoM % Growth

Hi,

I am trying to calculate YoY Income Growth 2015-2014 and MoM Volume Growth May 2015-May 2014 but am unable to get the calculation right.

This is my month Calc to bring in volume

Sum({\$<Year={ \$(=max(Year)) }>} [PIP_Profit_or_Loss])+ Sum({\$<Year={ \$(=max(Year)) }>} [Commission_Amount])

This is my month Calc to bring in volume

sum ({<[Month-Year] = {">=1<=5"}, [Month-Year] = {'Mar-2014','Mar-2015'}, Date =, Week=>} Volume)

Please be so kind as to assist me.

• ###### Re: YoY and MoM % Growth

Create a MonthID and use

for Previous year YTD growth:

Sum({<Year={\$(=Max(Year))}, MonthID= {"<=\$(=max({<Year={\$(=max(Year))}>} MonthID))"},Month= >}Volume)

/Sum({<Year={\$(=Max(Year)-1)}, MonthID= {"<=\$(=max({<Year={\$(=max(Year))}>} MonthID))"},Month= >}Volume)-1

for MOM Growth

Sum({<Year={\$(=Max(Year))}, MonthID= {"\$(=max({<Year={\$(=max(Year))}>} MonthID))"},Month= >}Volume)

/Sum({<Year={\$(=Max(Year)-1)}, MonthID= {"\$(=max({<Year={\$(=max(Year))}>} MonthID))"},Month= >}Volume)-1

you can also use unique auto number of a month to get the same MOM result

Sum({<MonthNumber= {\$(=max(MonthNumber)-12)},Month= >}Volume)

to get exactly the same month in the previous year.

• ###### Re: YoY and MoM % Growth

Hi Santhosh,

Thank you for the response but I still cant seem to get it right. It is either showing 100% for everything or 0, it is also taking away the other numbers that were in.

• ###### Re: YoY and MoM % Growth

please share a sample application to understand the issue

• ###### Re: YoY and MoM % Growth

Hi Santhosh,

My system does not allow me to send anything or attach anything. I have done a print screen of sample data, will this help?

Yolanda Richards

• ###### Re: YoY and MoM % Growth

Its working!!!

Thank you ☺

• ###### Re: YoY and MoM % Growth

Hi All.

I am new here and new to Qlik and I need some help.

I am having a problem doing MoM analysis. Below is the set analysis function that I am using. For YoY it is working perfectly, however, for MoM it is not. I cannot figure out why, any help is appreciated.

YoY Function that works as intended:

'Repo Charge-off YoY: '& Money(SUM({\$<Year={\$(=MAX(Year))}>}RepoGLChgoff)-SUM({\$<Year={\$(=MAX(Year)-1)}>}RepoGLChgoff),'\$ ,###.##')

MoM Function that does not work:

&Chr(13)&'Repo Charge-off MoM: '& Money(SUM({\$<Month={\$(=MAX(Month))}>}RepoGLChgoff)-SUM({\$<Month={\$((Month)-1)}>}RepoGLChgoff),'\$ ,###.##')

• ###### Re: YoY and MoM % Growth

What is in your Month field?  If it is just Jan-Dec then it won't work (as Dec of the previous year will be the max).  You need 1 Sep 2015 etc. for it to work.

The format of the field makes a difference in set analysis.  If you use the Date function in both your load and your set analysis then it should work better, so, in the load script;

Date(MonthStart(DateField), 'MMM-YY') as Month,

And then in the Set Analysis:

SUM({\$<Month={\$(=Date(MAX(Month), 'MMM-YY'))}>}RepoGLChgoff)

You also have bugs in the prior year, you need to take off a whole month, not 1, which would be a day:

Calculating a sequential month number in the load can make things simpler, something like.

(Year(DateField) * 12) + Month(DateField) -1 as MonthNo,

Check the example I posted to this thread back in June for some other prior period ideas.

Cheers,

Steve

• ###### Re: YoY and MoM % Growth

Thank you for the help Steve.

I was able to go back and create the variables, and when using the variables in the function provided above, and the information in the other thread I was able to get an operational MoM figure.

Thanks,

Mitch

• ###### Re: YoY and MoM % Growth

This application may help point you in the right direction:

QlikView App: Set Analysis - Prior Period Comparison

Steve

• ###### Re: YoY and MoM % Growth

Hope this helps

• ###### Re: YoY and MoM % Growth

I seem to have a problem with the coding of the date now

When I use this it works: =(max())

But when I use this for previous month =(max(-1))  It comes through as numbers.

How do I change this?

• ###### Re: YoY and MoM % Growth

instead of month =(max(-1))

use month=(max()-1)

• ###### Re: YoY and MoM % Growth

Thanks but its also not working

• ###### Re: YoY and MoM % Growth

post your exp here

• ###### Re: YoY and MoM % Growth

copy your complete expression. let us have a look.

• ###### Re: YoY and MoM % Growth

Thanks

My expression is : =sum({<MonthNumber= {\$(=max(MonthNumber-1))},Month= ,Year=>}Volume)

And my label formula is : =(max()-1) , I changed it to =(max()()-1)) but am getting ‘-‘

The one that works is as follows: =sum({<MonthNumber= {\$(=max(MonthNumber))},Month= ,Year=>}Volume)

With label: =(max())

• ###### Re: YoY and MoM % Growth

Try like below,

=sum({<MonthNumber= {"\$(=max(MonthNumber)-1)"},Month= ,Year=>}Volume)

• ###### Re: YoY and MoM % Growth

Use below expression:

=sum({<MonthNumber= {\$(=max(MonthNumber)-1)},Month= ,Year=>}Volume)

I did not understand what you are trying to display in label

And my label formula is : =(max()-1)

• ###### Re: YoY and MoM % Growth

I want the label to show Feb-2015 instead of 42155

June shows correctly as Jun-2015

Al the calculations are correct as you showed me.

• ###### Re: YoY and MoM % Growth

Use :

=Date(Max({<MonthNumber={\$(=max(MonthNumber)-1)},Month= ,Year= >}Date),'MMM-YYYY')

• ###### Re: YoY and MoM % Growth

Its working!!!!

Thank you ☺ ☺

Much appreciated