using today() in set expression

[maxmaier]

Hi there,

I am pretty new to qlik and I am struggling for too long with a certain problem that I just cannot solve on my own.

I am trying to get the average of all members for the current month.

I have started simple, and this expression works quite well, when I enter the month manually:

✅ Avg({1<Monat = {4}>} Mitarbeiter_gesamt) 

However: As soon as I want to make it dynamic it doesn't work any more. So far, I have tried:

❌ Avg({1<Monat = {"$(=month(today()))"}>} Mitarbeiter_gesamt) 

❌ Avg({1<Monat = {month(today())}>} Mitarbeiter_gesamt)

which did not work.

I have also tried to create a variable

➡️ current_month = month(today())

and use it like

❌ Avg({1<Monat = {current_month}>} Mitarbeiter_gesamt)

❌ Avg({1<Monat = {$(current_month)}>} Mitarbeiter_gesamt) 

which did not work either.

It would be great, if anyone could help me. Can't be too difficult to get a set expression with the current month...

Thanks a lot!

[Frank_E-W]

Month(Today()) will give output according to 

SET MonthNames='jan.;feb.;mars;apr.;maj;juni;juli;aug.;sep.;okt.;nov.;dec.';

in the main section of your script. The output from Month(Today()) must correspond to your date format for the data in the Monat column. 

Either set custom MonthNames (1;2;3;4...), or use the Date function to make Monat into a format that corresponds to your month names.

Try something like:

Avg({1<Monat= {"$(=Date(Month(Today()), 'M'))"}>} Mitarbeiter_gesamt)

[Vegar · Accepted solution]

As @Frank_E-W is saying Month() returns the values Jan, Feb,..Dec. according to you MonthNames variable. 

If your  [Monat]field is defined int the script using Month(), then you should be able to use:

Avg({<Monat = {'$(=month(today()))'}>} Mitarbeiter_gesamt)

If  [Monat] is an numeric 1,2,3 then you should be able to use: 

Avg({<Monat = {"$(=num(month(today())))"}>} Mitarbeiter_gesamt)

[maxmaier]

Thank you  @Frank_E-W  I have tried to use the date function, and it looked like it worked.

But then I realized, that the date function gives me a wrong number.

When I use month(today()) the result is 4,

but when I use date(month(today()), 'M') the result is 1 which is quite odd.

Do you have any idea why?

[maxmaier]

Thank you @Vegar ,

My [Monat] is numeric so I have used your second suggestion and it works!

Thanks!

[Vegar]

@maxmaier 

Let me try to explain why date(month(today()), 'M') always  will return 1 or 12.

The month() function returns the month number. Therefore month(today()) always return a number between 1 and 12.

The date() function formats a  numeric date value into a desired patten. I your case,  date(..., 'M'), format the numeric into 'M' (month number). The numeric value for todays data (2022-04-25) is 44676. The number 1-12 represents the dates between 1899-12-31 and 1900-01-11. These 12 dates are only January or December.