Using MonthName function in a set expression is not working as expected

[pranaview]

Hi Guys,

I have to get average of records for last 3 months. I was using below logic to get it but am not getting correct result.

=Avg({<MonthYear ={$(=MonthName(Addmonths(today(),-1))),$(=MonthName(Addmonths(today(),-2))),$(=MonthName(Addmonths(today(),-3)))}>}Field)

where the expression =MonthName(Addmonths(today(),-1)) will give you Dec 2018 and MonthYear field is MonthName(Date)

I think it's because of the $ expansion there might be something going wrong but i am not sure. 

Although when I use the following expression, i get the correct result.

=Avg({<MonthYear = {"=MonthYear =MonthName(Addmonths(today(),-1))","=MonthYear =MonthName(Addmonths(today(),-2))","=MonthYear =MonthName(Addmonths(today(),-3))"}>}Field)

I know i could have just used the second expression but I am curious why the first one did not work or is there any better solution to this problem than second expression.

Any input would be appreciated.

Cheers,

Pranav

[jonathandienst · Accepted solution]

You will need quotes as well.

=Avg({<MonthYear ={"$(=MonthName(Addmonths(today(),-1)))",
"$(=MonthName(Addmonths(today(),-2)))",
"$(=MonthName(Addmonths(today(),-3)))"}>} Field)

[pranaview]

Hi Jonty,

Thanks for the tip. Worked like a charm. Classic case of "Why didn't I think of that".

Regards,
Pranav