4 Replies Latest reply on Jul 8, 2016 8:50 AM by Justin Stanley

# Find Last Corresponding Values in Current Table

I have a data set that looks something like this:

ItemID
DateKey
11116/1/2016Monkeys
22226/1/2016
33336/1/2016
11116/2/2016Monkeys
22226/2/2016Frogs
33336/2/2016
11116/3/2016Monkeys
22226/3/2016Frogs
33336/3/2016Bears
11116/4/2016Monkeys
33336/4/2016Bears

And I need to figure out how to add what I assume would be a calculated column that, for each row, shows the last available for that key. So, starting off with that dataset, the result would be something like this:

ItemID
DateKeyMostRecentKey
11116/1/2016MonkeysMonkeys
22226/1/2016Frogs
33336/1/2016Bears
11116/2/2016MonkeysMonkeys
22226/2/2016FrogsFrogs
33336/2/2016Bears
11116/3/2016MonkeysMonkeys
22226/3/2016FrogsFrogs
33336/3/2016ZebrasBears
11116/4/2016MonkeysMonkeys
33336/4/2016BearsBears

And for the life of me, I can't figure out how to do it in Tableau. In Excel/DAX, I can get the result with something like this:

=CALCULATE(LASTNONBLANK(MyTable[Key],1), FILTER(MyTable, [ItemID] = EARLIER(MyTable[Key])))

But I'm pretty new to Tableau and have no idea what an equivalent function would look like. Any ideas?

• ###### 1. Re: Find Last Corresponding Values in Current Table

Not easy...and I don't have tableau handy so am freestyling this response somewhat. First you use an LOD calc to get the latest date for each Item:

{FIXED [ItemID]:MAX([Date])}

Then return the Key for each max date:

IF [Date] = {FIXED [ItemID]:MAX([Date])} THEN [Key] END

That may do it, but I suspect not, I think you may then need to use a table calc, something like WINDOW_MAX(MAX(IF [Date] = {FIXED [ItemID]:MAX([Date])} THEN [Key] END))

This earlier thread may help and I'll try and dig out other examples of this type of problem: How to lookup and update a new column with a particular row value

The LOD part should get you most of the way but I suspect the table calc may also be unavoidable and may (probably will) need tweaking from what I've suggested.

1 of 1 people found this helpful
• ###### 2. Re: Find Last Corresponding Values in Current Table

Justin,

Please see if the attached could be a first step for you.

I broke it up into three calculated fields.

The first returns all dates that have a key:

IF NOT(ISNULL([Key])) THEN [Date] END

The next uses a Level of Detail calculation to get the maximum date available for each Item ID:

{ FIXED [Item ID]:MAX([DateWithKey])}

Then the Key of that MaxDate is obtained:

{ FIXED [Item ID]:MAX(IF [Date]=[MaxDatePerID] THEN [Key] END)}

1 of 1 people found this helpful
• ###### 3. Re: Find Last Corresponding Values in Current Table

Andrew,