Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Qlik Sense - Select only a maximum date for a given record

I have seen a few previous posts regarding this topic, but I have been unable to find the solution for my seemingly simple problem.

I have a connection to a database table which contains a salary history for employees within the organization. For this particular visualization table, I would like to see only the maximum pay change date, and the associated Employee ID and Salary.

For example:

Employee Number     Start Date                    Salary

1                              01/01/2001                   10

1                              01/01/2002                   15

1                              01/01/2003                    20

What set expression would allow this table to only display the most recent row?

The following did not work, as the table still displayed the multiple rows per employee:

max(aggr(only("Start Date"), "Employee Number"))

AGGR(Max("Start Date"),"Employee Number"))

Thanks,

Chris

1 Solution

Accepted Solutions
xufei123
Valued Contributor

Re: Qlik Sense - Select only a maximum date for a given record

Dimension 1: Employee Number

Dimension 2: Start Date

Measure:

if([Start Date]=date(max(total <[Employee Number]> [Start Date])),

    sum(Salary),

    null()

)

uncheck Add-ons --> Show zero values.

2 Replies
xufei123
Valued Contributor

Re: Qlik Sense - Select only a maximum date for a given record

Dimension 1: Employee Number

Dimension 2: Start Date

Measure:

if([Start Date]=date(max(total <[Employee Number]> [Start Date])),

    sum(Salary),

    null()

)

uncheck Add-ons --> Show zero values.

Not applicable

Re: Qlik Sense - Select only a maximum date for a given record

Thank you for the help, this appears to be the solution.