Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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