Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
In Qlik Sense I have a table where I would like to show only specific rows.
In the table below I would like to show only the rows with the last Sale nr per date per Name. I have tried FirstSortedValue, but that only showed the highest value in column Sales nr, disregarding the Name.
Here is an example of how my table could look:
Name (dim) | Date (dim) | Hour(dim) | Sale nr (measure) |
---|---|---|---|
James | 21-10-2017 | 10:45 | 1 |
James | 21-10-2017 | 11:35 | 2 |
Colin | 21-10-2017 | 08:30 | 1 |
James | 21-10-2017 | 12:34 | 3 |
Colin | 21-10-2017 | 09:10 | 2 |
Colin | 21-10-2017 | 14:40 | 3 |
James | 21-10-2017 | 13:00 | 4 |
James | 22-10-2017 | 08:00 | 1 |
Colin | 22-10-2017 | 09:30 | 1 |
Colin | 22-10-2017 | 10:15 | 2 |
James | 22-10-2017 | 09:00 | 2 |
James | 22-10-2017 | 10:00 | 3 |
This is how I want it to look:
Name | Date | Hour | Last Sale nr |
---|---|---|---|
James | 21-10-2017 | 13:00 | 4 |
Colin | 21-10-2017 | 14:40 | 3 |
James | 22-10-2017 | 08:00 | 3 |
Colin | 22-10-2017 | 10:15 | 2 |
If anyone could help me out with this it would be much appreciated as I do not know how to set up this expression (or script).
Regards.
Nina
Try this
Dimension
Name
Date
Measure
Max(Hour)
FirstSortedValue([Last Sale nr], -Hour)
Assuming you want 10:00 in the third row for Hour instead of 08:00
Try this
Dimension
Name
Date
Measure
Max(Hour)
FirstSortedValue([Last Sale nr], -Hour)
Assuming you want 10:00 in the third row for Hour instead of 08:00
Hi!
Thank you! That worked out great 🙂
I needed to remove some extra columns though that I did not include in the example.
I have a column stating in which shop the sale was made - ShopName - do you know how it is possible to get the ShopName for the Last Sale nr as well?
Name | Date | Hour | Salenr | ShopName | |
James | 21.10.2017 | 10:45 | 1 | A | |
James | 21.10.2017 | 11:35 | 2 | C | |
Colin | 21.10.2017 | 08:30 | 1 | A | |
James | 21.10.2017 | 12:34 | 3 | C | |
Colin | 21.10.2017 | 09:10 | 2 | A | |
Colin | 21.10.2017 | 14:40 | 3 | B | |
James | 21.10.2017 | 13:00 | 4 | B | |
James | 22.10.2017 | 08:00 | 1 | A | |
Colin | 22.10.2017 | 09:30 | 1 | C | |
Colin | 22.10.2017 | 10:15 | 2 | D | |
James | 22.10.2017 | 09:00 | 2 | D | |
James | 22.10.2017 | 10:00 | 3 | A |
This was solved by adding ShopName as Dimension and adding Limitation that calculated on measure Max(Hour),Fixed Number Top 1.