Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

rerickson
New Contributor

Max Date

The Data Set I'm working with resembles this:

User           Import Date          Last Login Date          Days Since Last Login

Mary           5/2/2018                4/9/2018                     51

Mary           4/25/2018              4/9/2018                     51

Mary           4/9/2018                4/9/2018                     51

Mary           4/3/2018                3/262018                    65

Mary           2/26/2018              2/23/2018                   96

Mary           2/17/2018              2/8/2018                     111

Jerry           5/2/2018                4/11/2018                    49

Jerry           4/25/2018              4/9/2018                     51

Jerry           4/9/2018                4/9/2018                     51

Jerry           4/3/2018                3/262018                    65

Jerry           2/26/2018              2/23/2018                   96

Jerry           2/17/2018              2/8/2018                     111

Tony            4/25/2018              4/1/2018                     43

I have two requirements:

First, in the Chart on the Sheet, I need to return the row that contains the most recent Import Date for each User.  For example:

User           Import Date          Last Login Date          Days Since Last Login

Mary           5/2/2018                4/9/2018                     51

Jerry           5/2/2018                4/11/2018                   49

Tony           4/25/2018              4/1/2018                     43

Second, I need another Chart that returns the rows where the Import Date for the User equals the most recent Import Date in the entire data set.  For example:

User           Import Date          Last Login Date          Days Since Last Login

Mary           5/2/2018                4/9/2018                     51

Jerry           5/2/2018                4/11/2018                   49

I've try RangeMax() and Aggr() functions, but I can't get them to work.

Thank you for your help

5 Replies
chriscammers
Contributor III

Re: Max Date

The key to solution 1 is the function FirstSortedValue.

Then for your second requirement you need to use some Set analysis to limit your results to the rows with the highest value of the Import Date.

Date(Max({<[Import Date] = {'$(=date(Max([Import Date])))'}>}[Import Date]))

I'm attaching a qvf, I hope this helps. !

rerickson
New Contributor

Re: Max Date

Thanks Chris, I appreciate your help.  I'm placing the expression

1. Date(FirstSortedValue(distinct [Email Address],-[Import Date]))

2. Date(Max({<[Import Date] = {'$(=date(Max([Import Date])))'}>}[Import Date]))

in the "Edit expression" screen of the [Import Date] field on the Chart, but receiving an "Invalid Dimension" error.

The expression validation in the bottom left indicates "OK", but I can figure out where I've gone wrong.  Do you have any suggestions?

Thanks again.

rerickson
New Contributor

Re: Max Date

I have also tried adding it to the "Data handling" section of Add-ons, but this does not limit the data returned in the Chart.

Re: Max Date

Try this?

Capture.PNG

chriscammers
Contributor III

Re: Max Date

In the tables you are displaying the import Date, First login and Days since last login are all Measures.