Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, I have a Filter Pane that contains Years, and I want to be able to filter the data for any specific year. But when I select current year (2020 in this case) I want it to only show the dates up to Today's date. Is there a way to do this?
I am currently using this expression to count employees currently active at the end of each year (from a link table with each row containing a unique combination of date and ID number if While EndDate is lower or same as ReferenceDate). (Following this example: https://community.qlik.com/t5/Qlik-Design-Blog/Creating-Reference-Dates-for-Intervals/ba-p/1463944)
But I want to be able to see current active employees too, not the total after people quit later in the year, if that makes sense?
Count(Distinct {<ReferenceDate={'$(=max(ReferenceDate))'}>} ID)
I think I figured it out.
I did it the long and slow way with:
IF
(
Only(Year)= Year(Today()),
Count(Distinct {<ReferenceDate={"$(=Today())"}>} [AnställningNr.]),
Count(Distinct {<ReferenceDate={"$(=Max(ReferenceDate))"}>} [AnställningNr.])
)
What I also noticed is that because my dates are written YYYY-MM-DD when I used an = before the "$..." it tries to calculate the dates as numbers, ie. 2020 minus 03 minus 10.
I guess I should learn more about Date best practice ^^
I believe if you add a condition to your set analysis to not show dates later than Today it may work; or maybe I am misunderstanding your needs / data model.
Count(Distinct {<ReferenceDate={'$(=max(ReferenceDate))'}, ReferenceDate={'=<$(=date(today())'}>} ID)
With your expression I only get 0 as the value. And when I tried to change the order of the filters:
Count(Distinct {<ReferenceDate={'=<$(=date(today())'}, ReferenceDate={'$(=max(ReferenceDate))'}>} ID)
I get the same results as before, just max date values even on current year. Seems the Max() filter overrides the today() filter?
Right now Im thinking maybe set a flag in the script as: if(ReferenceDate <=today(),1,0) As LessThanTodayFlag.
And then:
Count(Distinct {<LessThanTodayFlag={'1'}, ReferenceDate={'$(=max(ReferenceDate))'}>} ID)
But Im not sure if that will also run into the same problem?
This should work fine based on what I understand
I think I figured it out.
I did it the long and slow way with:
IF
(
Only(Year)= Year(Today()),
Count(Distinct {<ReferenceDate={"$(=Today())"}>} [AnställningNr.]),
Count(Distinct {<ReferenceDate={"$(=Max(ReferenceDate))"}>} [AnställningNr.])
)
What I also noticed is that because my dates are written YYYY-MM-DD when I used an = before the "$..." it tries to calculate the dates as numbers, ie. 2020 minus 03 minus 10.
I guess I should learn more about Date best practice ^^