Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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)
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)
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)
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?
Thank you @Vegar ,
My [Monat] is numeric so I have used your second suggestion and it works!
Thanks!
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.