6 months rolling set analysis expression not working with aggr function

[Anonymous]

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..

[sunny_talwar]

Sent you a private message

[Anonymous]

Its working now sunny.

Thank you so much for your timely help..

[sunny_talwar]

Great

[sam_grounds]

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

[sam_grounds]

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.