YoY and MoM % Growth

[#1]

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.

[stevedark]

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:

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

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

[Anonymous]

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