Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I need to the show the cumulative sums of last 3 months over last 12 months.
For example, iIf I select July 2016, I need to plot the sum(last 3 months may, June, July) of each month over a period of 12 months (Like we did for July.)
Hope am clear.
I tried using the month as dimension and using set analysis get the sum() of 3 months using
sum(aggr(rangesum(above(total sum({<Month=>}amount),0,3)),Month)),
and then trying to do set analysis something like (monthstart={">= $(=num((Max(AddMonths(MonthStart((month)),-11))))) <=$(=num(Max((MonthStart((month)))))) "). But its give allocated memory exceeded error.
Please help
May be with calculated dimension
If(MonthYear >= AddMonths(Max(TOTAL MonthYear), -12) and MonthYear <= Max(TOTAL MonthYear), MonthYear)
Expression
RangeSum(Above(Sum({<Month=, monthstart>} amount), 0, 3))
And I can't keep the sum of last 3 months to be calculated in the script as I need to keep it dynamic
I would recommend using an as of table
So do i need to get the 3 months aggregate using sum({<monthdiff="<3">}value) and then use this in the set analysis??
Are you doing this where MonthYear is your dimension... You might be able to do this
RangeSum(Above(Sum({<Month=, monthstart>} amount), 0, 3)) * Avg({<monthstart = {"$(='>=' & Date(MonthStart(Max(monthstart), -11), 'MMM-YYYY') & '<=' & Date(Max(monthstart), 'MMM-YYYY'))"}>} 1)
Sunny, Am using month num as dimension and already has 3 other expressions plotted over
Avg({<monthstart = {"$(='>=' & Date(MonthStart(Max(monthstart), -11), 'MMM-YYYY') & '<=' & Date(Max(monthstart), 'MMM-YYYY'))"}>} 1). Giving 12 months . There is a month selected in the filter. Your expression is getting value only for selected month .
Can you share one of the three expressions (or may be all three) which are working?
I have 3 more normal expressions showing data for last 12 months from a selected month. I used month year as dimension and used set analysis like (monthstart={">= $(=num((Max(AddMonths(MonthStart((month)),-11))))) <=$(=num(Max((MonthStart((month)))))) ") for these 3 expressions.
Now i need to show the 3 months aggregated value in the same chart.
Please help
If this has worked for your other expressions
monthstart={">= $(=num((Max(AddMonths(MonthStart((month)),-11))))) <=$(=num(Max((MonthStart((month)))))) ")
Then this should work also.... or share a sample
RangeSum(Above(Sum({<Month=, monthstart>} amount), 0, 3)) * Avg({<monthstart={">= $(=num((Max(AddMonths(MonthStart((month)),-11))))) <=$(=num(Max((MonthStart((month))))))"}>} 1)
Sunny,
If we don't keep the filter on month start in Rangesum() part its taking all the month into consideration.
Year=, Month=, Quarter=,Day=,monthstart={">= $(=num((Max(AddMonths(MonthStart((month)),-11))))) <=$(=num(Max((MonthStart((month)))))) ")
is the complete set am using. (If it makes any difference)
Please let me know if am missing anything