How to set specific critera for which rows to display?

[nina_jensen]

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

[sunny_talwar · Accepted solution]

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

[nina_jensen]

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

[nina_jensen]

This was solved by adding ShopName as Dimension and adding Limitation that calculated on measure Max(Hour),Fixed Number Top 1.