Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

nina_jensen
New Contributor III

How to set specific critera for which rows to display?

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)
James21-10-201710:451
James21-10-201711:352
Colin21-10-201708:301
James21-10-201712:343
Colin21-10-201709:102
Colin21-10-201714:403
James21-10-201713:004
James22-10-201708:001
Colin22-10-201709:301
Colin22-10-201710:152
James22-10-201709:002
James22-10-201710:003

This is how I want it to look:

NameDateHourLast Sale nr
James21-10-201713:004
Colin21-10-201714:403
James22-10-201708:003
Colin22-10-201710:152

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

1 Solution

Accepted Solutions
MVP
MVP

Re: How to set specific critera for which rows to display?

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

3 Replies
MVP
MVP

Re: How to set specific critera for which rows to display?

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
New Contributor III

Re: How to set specific critera for which rows to display?

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?

 

NameDateHourSalenrShopName
James21.10.201710:451A
James21.10.201711:352C
Colin21.10.201708:301A
James21.10.201712:343C
Colin21.10.201709:102A
Colin21.10.201714:403B
James21.10.201713:004B
James22.10.201708:001A
Colin22.10.201709:301C
Colin22.10.201710:152D
James22.10.201709:002D
James22.10.201710:003A
nina_jensen
New Contributor III

Re: How to set specific critera for which rows to display?

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

Community Browser