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

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

pranaview — Sat, 16 Nov 2024 21:36:08 GMT

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

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

jonathandienst — Tue, 15 Jan 2019 10:01:30 GMT

You will need quotes as well.

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

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

pranaview — Tue, 15 Jan 2019 10:17:37 GMT

Hi Jonty,

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

Regards,
Pranav