Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
avg(aggr(((Sum(a/b))*c),[emp ID],Month_Year))
This was my expression initially in the chart, then i got a requirement of 6 months rolling for that chart, i used the below set analysis expression, which is working fine in all expressions except the above one, where i have used aggr
Its not working with the aggr function.
{<Month_Year = {">=$(=Date(addmonths(Max(Month_Year), -5), 'MMM-YYYY')) <=$(=Date(addmonths(Max(Month_Year), 0), 'MMM-YYYY'))"} >}.
Please help me to find out how can i do 6 months rolling for this expression
Dimension - Month_Year
avg(aggr(((Sum(a/b))*c),[emp ID],Month_Year))
thanks..
Sent you a private message
Its working now sunny.
Thank you so much for your timely help..
Great
I encountered a similar problem a while back, the way I solved it was to create a new, numeric 'MonthNo' field to add +1 for each unique month in the calendar table and then use that for a simple set analysis.
The line of script in my calendar table was this...
AutoNumber(MonthStart(TempDate),'MonthNo') As MonthNo
('TempDate' was my auto-generating date field - but you could bolt this onto an existing calendar table by replacing TempDate with your full date field).
You could then simply use {$<MonthNo={">=$(=max(MonthNo)-5)"}>} as your set analysis to give you the last rolling 6 months.
Hope this helps,
Sam
Also, don't forget to use set analysis in your outer aggregation function. Avg (your outer function) needs set analysis as well as the inner functions (sum etc.) otherwise the data may not pass between the inner/outer functions correctly.