Suppressing Rows based on an Expression (Measure) Value

[joshm43]

Hello -

I am building a straight table for a user activity report.  I want to be able to see only the last login date by user.  Here is what my notional table looks like.

username and LoginDate are dimensions.  The other two are measures - I've included the expression in "".

username	LoginDate	Max Login Date /  "MAX(TOTAL <username> LoginDate) "	Date Test / "=IF(LoginDate = MAX(TOTAL <username> LoginDate), 1, 0)"
Agent1	8/3/2021	8/3/2021	1
Agent1	7/31/2021	8/3/2021	0
Agent1	6/15/2021	8/3/2021	0
Agent1	5/1/2021	8/3/2021	0
Agent2	8/2/2021	8/2/2021	1
Agent2	7/5/2021	8/2/2021	0
Agent3	7/31/2021	7/31/2021	1
Agent3	7/21/2021	7/31/2021	0
Agent3	7/11/2021	7/31/2021	0

What I want to be able to do is suppress those rows where the Date Test value is "0" (I can change this to a Null value if needed), so the table would look like:

username	LoginDate	Max Login Date /  "MAX(TOTAL <username> LoginDate) "	Date Test / "=IF(LoginDate = MAX(TOTAL <username> LoginDate), 1, 0)"
Agent1	8/3/2021	8/3/2021	1
Agent2	8/2/2021	8/2/2021	1
Agent3	7/31/2021	7/31/2021	1

I am using an Enterprise version of Qlik Sense and do not have access to the load scripts, so whatever needs to be done needs to be done within the Table.

Any thoughts or suggestions are greatly appreciated.

Thanks, Mitchell

[kaushiknsolanki · Accepted solution]

Hi,

what you need is a function called "FirstSortedValue". 

Create a table with Username as Dimension and below expression.

Date(FirstSortedValue(LoginDate,LoginDate*-1))