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: 
stevelord
Specialist
Specialist

Expression help, if most current date for a user within a straight table, 1)

Hi Community,

I have a straight table showing values users have for a certain activity, and most users have only the one value in a selected date range, but some have more than one and I want the straight table to only show the most current value per user.  (save me from having to dedupe on user id in excel after I export.  Save me from exporting anything actually.)  I don't think I can do this in script because date ranges can be in the past or anything anyone selects really.  I just need it on this one straight table.

Data looks like this in the straight chart

User ID    Date          Value    Total Users     Total Records

                                                 2                      5

123        1/5/2015       3.5

123        12/4/2014     3.3

123        11/3/2014     3.6

456        1/10/2015     2.9

456         1/5/2015      3.1

I want the table to look like this

User ID    Date       Value   MostCurrent    Total Users     Total Records(Record count optional if 1:1 with users)

                                                                    2                     2

123         1/5/2015   3.5       1

456         1/10/2015  2.9       1

I'd check a checkbox or something to suppress when null on that MostCurrent column after that.

1 Solution

Accepted Solutions
maxgro
MVP
MVP

maybe with 2 expressions

max(Date)

FirstSortedValue(Value, -Date)

View solution in original post

8 Replies
alexandros17
Partner - Champion III
Partner - Champion III

There is not such that flag,

you can (in the script) find the max date

Load max(date) as max, UserID, 1 as flag from ... Group by UserID;

now you have a flag to manage in your object (straight table) only records where date is max

let me know

stevelord
Specialist
Specialist
Author

I think the solution would involve set analysis {<>} stuff inside the expression to let it look at the user's dates within the table and return a 1 if it happens to be the most current date within the table.

I can't wing it in script, because I don't want the most current date in the database for that user, just the most current date within the selected date range.  So for instance, I could select 2012 and get the most current record in that range for everyone, then 2013 and get the most current record there, and 2014 to get the most current there, etcetera.

jerem1234
Specialist II
Specialist II

Couldn't you use just a simple max(Date)? Or if you need a dimension, use something like aggr(max(Date), UserID) as a calculated dimension.

Is the field you select a date range for linked to your Date field?

If it is, as long as you don't use a 1 in the set analysis, it should filter the max function based on selections.

Could you post an example of your situation or explain a bit more?

Hope this helps!

maxgro
MVP
MVP

maybe with 2 expressions

max(Date)

FirstSortedValue(Value, -Date)

stevelord
Specialist
Specialist
Author

Okay, I have a calculated dimension for =aggr(max(TestDate), UserID) that's populating the correct most current date value for each user within the date range selected.  I'm having difficulty making an expression to compare the testdate dimension to the max testdate calculated dimension though.  TestDate is one of many date fields I have- it is formatted and functions as a date field.  (I have others like ImportedDate, various other date fields for other events/activities.)

The listboxes are linked to the TestDate field used in this chart.  So for instance, I have a field defined in script for year(TestDate) as TestYear, and a listbox for that etcetera.  I could click 2014 and make below cut to the 2014 records, and the max date goes to 12/4/2014 at that point.  I feel like I'm an inch from having this solved now.   (I can export and remove duplicates on user id in excel, but want to make that effort go away.)

This is what the straight table looks like now

(MaxDate is a calculated dimension, Record count is an expression, others are dimensions)

User ID    TestDate   MaxDate      Value    Record Count

123        1/5/2015    1/5/2015          3.5        1

123        12/4/2014   1/5/2015          3.3         1

123        11/3/2014    1/5/2015         3.6           1

456        1/10/2015    1/10/2015        2.9         1

456         1/5/2015     1/10/2015         3.1          1

jerem1234
Specialist II
Specialist II

Can you be more elaborate of what u mean when u say u want to compare? do u want to add another expression that you need calculated? What is the final table you are looking for?

I don't see a need for both field: TestDate and MaxDate, if you are looking to have one value for each ID. Just get rid of the TestDate field and leave MaxDate. What does that give?

stevelord
Specialist
Specialist
Author

This appears to be working consistently for multiple users and time periods as well as individual user selections with multiple records to choose from.  I also had to physically removed the TestDate dimension, and am just using max(TestDate) as the TestDate field for the users now.  Then the second function works independently to grab the correct value associated with the most current testdate in the selection for the users listed in the straight table.

stevelord
Specialist
Specialist
Author

TestDate dimension removed from the straight table that is.  It's still around for the expressions and such.