topic Re: YoY and MoM % Growth in QlikView

YoY and MoM % Growth

Mon, 15 Jun 2015 09:09:25 GMT

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

santhoo_san — Mon, 15 Jun 2015 11:44:35 GMT

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

Mon, 15 Jun 2015 12:17:15 GMT

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

santhoo_san — Mon, 15 Jun 2015 12:43:57 GMT

please share a sample application to understand the issue

Re: YoY and MoM % Growth

Mon, 15 Jun 2015 13:07:53 GMT

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

stevedark — Mon, 15 Jun 2015 13:13:40 GMT

This application may help point you in the right direction:

QlikView App: Set Analysis - Prior Period Comparison

Steve

Re: YoY and MoM % Growth

santhoo_san — Mon, 15 Jun 2015 13:53:34 GMT

Hope this helps

Re: YoY and MoM % Growth

Wed, 17 Jun 2015 06:45:30 GMT

Its working!!!

Thank you ☺

Re: YoY and MoM % Growth

Wed, 17 Jun 2015 07:05:16 GMT

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

santhoo_san — Wed, 17 Jun 2015 07:11:29 GMT

instead of month =(max(-1))

use month=(max()-1)

Re: YoY and MoM % Growth

Wed, 17 Jun 2015 07:17:17 GMT

Thanks but its also not working

Re: YoY and MoM % Growth

Wed, 17 Jun 2015 07:18:53 GMT

post your exp here

Re: YoY and MoM % Growth

santhoo_san — Wed, 17 Jun 2015 07:21:41 GMT

copy your complete expression. let us have a look.

Re: YoY and MoM % Growth

Wed, 17 Jun 2015 07:40:05 GMT

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

Wed, 17 Jun 2015 07:41:52 GMT

Try like below,

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

Re: YoY and MoM % Growth

santhoo_san — Wed, 17 Jun 2015 07:43:03 GMT

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

Wed, 17 Jun 2015 07:46:05 GMT

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

santhoo_san — Wed, 17 Jun 2015 07:51:21 GMT

Use :

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

Re: YoY and MoM % Growth

Wed, 17 Jun 2015 07:55:06 GMT

Its working!!!!

Thank you ☺ ☺

Much appreciated

Re: YoY and MoM % Growth

Thu, 24 Sep 2015 19:07:26 GMT

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),'$ ,###.##')