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

[Anonymous]

this cant't work because set analysis have text values as input.

If you use >= or <= then you have to give the set analysis a format that the

Automatic Number Interpretation can convert into a numerical value.

[Anonymous]

you could try expression search instead for example:

{<Month_Year = {"=

(Month_Year>=$(=Date(addmonths(Max(Month_Year), -5), 'MMM-YYYY')))

and

(Month_Year<=$(=Date(addmonths(Max(Month_Year), 0),  'MMM-YYYY'))"})

>}

[Anonymous]

I dont understand on what you are telling as TEXT .

(Sum({<Month_Year = {">=$(=Date(addmonths(Max(Month_Year), -5), 'MMM-YYYY')) <=$(=Date(addmonths(Max(Month_Year), 0),  'MMM-YYYY'))"} >} a/b))

The above expression will display the sum(a/b) as rolling six months in the chart.

Whereas, if i use the same set analysis exp with aggr function

avg(aggr(((Sum({<Month_Year = {">=$(=Date(addmonths(Max(Month_Year), -5), 'MMM-YYYY')) <=$(=Date(addmonths(Max(Month_Year), 0),  'MMM-YYYY'))"} >} a/b))*c),[emp ID],Month_Year))

Its not working.

[#5]

try this

aggr(Sum({<Month_Year ={">=$(=Date(addmonths(Max(Month_Year), -5), 'MMM-YYYY'))<=$(=Date(addmonths(Max(Month_Year), 0),'MMM-YYYY'))"} >}a/b),[emp ID],Month_Year)

[Anonymous]

sorry, my mistake, try:

avg(aggr(((Sum({<Month_Year = {">=$(=Date(addmonths(Max(Month_Year), -5), 'MMM-YYYY')) <=$(=Date(addmonths(Max(Month_Year), 0),  'MMM-YYYY'))"} >} a/b))

*only({<Month_Year = {">=$(=Date(addmonths(Max(Month_Year), -5), 'MMM-YYYY')) <=$(=Date(addmonths(Max(Month_Year), 0),  'MMM-YYYY'))"} >} c)

),[emp ID],Month_Year))

could also be that you have to place the set modifier additionally within avg like

avg({<Month_Year = {">=$(=Date(addmonths(Max(Month_Year), -5), 'MMM-YYYY')) <=$(=Date(addmonths(Max(Month_Year), 0),  'MMM-YYYY'))"} >}

aggr(((Sum({<Month_Year = {">=$(=Date(addmonths(Max(Month_Year), -5), 'MMM-YYYY')) <=$(=Date(addmonths(Max(Month_Year), 0),  'MMM-YYYY'))"} >} a/b))

*only({<Month_Year = {">=$(=Date(addmonths(Max(Month_Year), -5), 'MMM-YYYY')) <=$(=Date(addmonths(Max(Month_Year), 0),  'MMM-YYYY'))"} >} c)

),[emp ID],Month_Year))

[Anonymous]

Not a problem. Thank you.

Tried your expressions, but it is not working.

[Anil_Babu_Samineni]

May be this?

avg(aggr(((Sum({<Month_Year = {">=$(=Date(addmonths(Max(Month_Year), -5), 'MMM-YYYY')) <=$(=Date(addmonths(Max(Month_Year), 0),  'MMM-YYYY'))"} >} (a/b)))*c),[emp ID],Month_Year))

[Anonymous]

please prepare a sample

[Anonymous]

Thanks anil,

Its not working