Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Greetings,
i have data that looks like this:
Report Month | Gate 1 | Gate 2 | Gate 3 | Gate 4 | Gate 5 | Gate 6 | Gate 7 | Gate 8 |
Jan | 13.05.2016 | 03.03.2017 | 03.03.2017 | 29.09.2017 | 27.10.2017 | 30.03.2018 | 29.12.2018 | 14.06.2019 |
Feb | 13.05.2016 | 03.03.2017 | 03.03.2017 | 30.06.2017 | 31.07.2017 | 30.03.2018 | 29.12.2018 | 14.06.2019 |
Mär | 13.05.2016 | 03.03.2017 | 31.05.2017 | 30.06.2017 | 31.07.2017 | 30.03.2018 | 29.12.2018 | 14.06.2019 |
Apr | 15.06.2016 | 31.05.2017 | 30.06.2017 | 31.12.2017 | 15.02.2018 | 30.03.2018 | 29.12.2018 | 14.06.2019 |
Mai | 30.06.2016 | 31.05.2017 | 30.06.2017 | 31.12.2017 | 15.02.2018 | 30.03.2018 | 29.12.2018 | 14.06.2019 |
Jun | 01.06.2016 | 30.04.2017 | 30.04.2017 | 31.12.2017 | 15.02.2018 | 30.03.2018 | 29.12.2018 | 14.06.2019 |
Jul | 30.04.2017 | 30.06.2017 | 31.12.2017 | 15.02.2018 | 30.03.2018 | 29.12.2018 | 14.06.2019 | |
Aug | 31.08.2017 | 31.12.2017 | 15.02.2018 | 30.03.2018 | 29.12.2018 | 14.06.2019 | ||
Sep | 31.08.2017 | 31.12.2017 | 15.02.2018 | 31.08.2018 | 29.12.2018 | 14.06.2019 | ||
Okt | 15.02.2018 | 31.05.2018 | 31.08.2018 | 29.12.2018 | 14.06.2019 | |||
Nov | 15.02.2018 | 31.05.2018 | 31.08.2018 | 30.04.2019 | 14.06.2019 | |||
Dez | 31.05.2018 | 31.08.2018 | 30.04.2019 | 14.06.2019 |
And I want to make a table that shows the last given date for each "Gate" that is not NULL, like this:
Gates | Latest Date |
1 | 01.06.2016 |
2 | 30.04.2017 |
3 | 31.08.2017 |
4 | 15.02.2018 |
5 | 31.05.2018 |
6 | 31.08.2018 |
7 | 30.04.2019 |
8 | 14.06.2019 |
To get the Gates into Rows, I used ValueList(...). With that i planned on creating custom measures for each Gate by using if-statements (since they're in different Fields in the Source Data), like:
if(ValueList(...)='1'
,Get latest date from Field "Gate 1"
,if(ValueList(...)='2'
,Get latest date from Field "Gate 2"
,if(ValueList(...)='3'
and so on...
But how do I get the latest value from each field? Max([Gate X]) doesn't work, because newer dates may be earlier than the ones before (see "Gate 1").
I created a calculated Field that translates the month names into numbers 1 to 12, so that i could use FirstSortedValue() in a For...Next-Statement to just check every row with isnull() counting down from 12, but For...Next is not available as a chart function in Qlik Sense.
So next i tried checking every row via chained if-statements, but the script won't allow the necessary amount of characters for a single measure (would have been 11 IFs for each of the 8 Gates).
So my question is: What function could i put in place of "Get latest date from Field 'Gate X'" for it to work?
May be this in that case
FirstSortedValue({<[Gate X] = {"*"}>} [Gate X], -[Report Month as number])
May be this
Max({<[Gate X] = {"*"}>}[Gate X])
Thank you for the reply, but that doesn't do it unfortunately.
As stated, Max() doesn't work because Gate-Dates given in later Report Months may be earlier than the ones before. For "Gate 1" your suggestion returns the highest value (30.06.2016 from May) instead of the desired 01.06.2016 from June.
I had the idea of using count([Gate X]) as a rank modifier for FirstSortedValue(), i.e.
FirstSortedValue([Gate X], [Report Month as number], count([Gate X]),
so i would always get the date in the lowest filled row. But FirstSortedValue() doesn't seem to accept functions as a rank. Just typing the correct number works, but using count() only returns NULL.
May be this in that case
FirstSortedValue({<[Gate X] = {"*"}>} [Gate X], -[Report Month as number])
Hi,
I am not sure about your data model but it will work for you I guess.
Exp: FirstSortedValue({<date={"=len(date)>0"}>} date, -RM_Num)
Works like a charm. Thank you a lot!
The Table I posted originally is what the raw data looks like.
Which means this doesn't work for my particular case, but your solution would be correct if the raw data looked like in your picture. But still, thanks a lot! Because I do have other data that is very similar to what you posted and your function will help me with that. It's like you looked into the future and solved a problem i didn't know i had yet!