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]

and the exactly same expression is surely working without set modifiers in the same chart?

[Anil_Babu_Samineni]

Why? May be share application to test

[Anonymous]

Hi,

I have attached the sample application and explained also.

Please try this and let me know if you have doubts.

Thank you

[Anonymous]

Hi anil . I have attached the application

[sunny_talwar · Accepted solution]

May be this

num(Avg({<Month_Year>}Aggr(((Sum({<Month_Year = {"=Date#(Only({1} Month_Year), 'MMM-YYYY') >= AddMonths(Max(TOTAL Date#(Month_Year, 'MMM-YYYY')), -5) and Date#(Only({1} Month_Year), 'MMM-YYYY') <= Max(TOTAL Date#(Month_Year, 'MMM-YYYY'))"}>}a/b))*Only({<Month_Year>} c)), empid, Month_Year)

),'#.##')

[Anonymous]

that's what I tried to explain at the beginning,your Month_Year field wasn't a dual, so it couldn't work...

[Anonymous]

... inline values create just text, ... they only create duals, that are recognized by the automatic number interpretation...

so you'll have to add the numerical representation with date#(...) either in Script or in Frontend as stalwar1‌ did it!

[Anonymous]

Thank you so much sunny.

I have again a different set of data.

I have attached the sample application. Can you pls help me out with this.

Now i have different a values and b values for one employee for the same month.

[Anonymous]

Thank you so much Robin

I have again a different set of data.

I have attached the sample application in reply to sunny. Can you pls help me out with that.

Now i have different a values and b values for one employee for the same month.

[Anonymous]

num(Avg({<Month_Year>}Aggr(((Sum({<Month_Year = {"=Date#(Only({1} Month_Year), 'MMM-YYYY') >= AddMonths(Max(TOTAL Date#(Month_Year, 'MMM-YYYY')), -5) and Date#(Only({1} Month_Year), 'MMM-YYYY') <= Max(TOTAL Date#(Month_Year, 'MMM-YYYY'))"}>}a/b))*Only({<Month_Year>} c)), empid, Month_Year)

),'#.##')

Will this expression works now ??