Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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.
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!
maybe with 2 expressions
max(Date)
FirstSortedValue(Value, -Date)
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
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?
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.
TestDate dimension removed from the straight table that is. It's still around for the expressions and such.