Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
Partner - Specialist
Partner - Specialist

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. !

Anonymous
Not applicable
Author

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.

Anonymous
Not applicable
Author

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.

Anil_Babu_Samineni

Try this?

Capture.PNG

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
chriscammers
Partner - Specialist
Partner - Specialist

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