Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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. !
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.
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.
Try this?
In the tables you are displaying the import Date, First login and Days since last login are all Measures.