5 Replies Latest reply on Oct 25, 2018 7:41 AM by Ombir Rathee

# MAX of one field based on another?

I've attached a workbook. I'm really not sure what is needed here, whether it's an LOD calc or if it's easier than that.

I have a parameter called Select Date, and the Calculated Field I'm trying to figure out is Current Category. At all times, I want the Current Category field to be identical for each Project ID, and I want that to equal the most recent Category. So I want the MAX of Category, but based on the date, not the MAX of Category by alphabetical.

If I choose 4/17/18, I want ID 353 to show 20-Blue for each record, and ID 400 to show 20-Blue.

If I choose 7/20/18, I want ID 353 to show 10-Red for each record, and ID 400 to show 20-Blue.

Please let me know if this needs any further clarification.

Thanks!

• ###### 1. Re: MAX of one field based on another?

Hi Michael,

I'm having trouble understanding your logic. How do you identify the 4/17/18 ID 353 to show 20-blue etc ?

We can use the show missing values function in Tableau to expose the date, but there are no records associated with it. In the 17/4/2018 example there is a record on the 18/04/2018 - is that what you are looking for ?

I'm also not clear on which is the Max value you want to return, there seems to only be one instance where this might apply, which is the dual record on 16/06/2018.

Grateful if you could extrapolate this a little further.

HTH

Peter

• ###### 2. Re: MAX of one field based on another?

Thanks for the reply, Peter. Let me clarify. It looks like you switched the first two columns, which makes it harder to explain, so please refer to my original when I have ID as the left most column.

To answer your first question, if I select 4/17, I want it to return 20-Blue because that is the most recent category for ID 353. The category didn't change for ID 353 until 4/18.

If I select 5/17, I want it to return 30-Green because the category was changed to 30-Green on 5/16. So for all records with ID 353, I want this Current Category to read 30-Green.

Does that help?

• ###### 3. Re: MAX of one field based on another?

Like this ?

• ###### 4. Re: MAX of one field based on another?

Thanks for the reply, but I can't open your workbook because it's in a newer version than I have.

However, I did find the answer to my question, which is a complex LOD formula.

{EXCLUDE [Date],[Category] : MAX(IF [Date] = {EXCLUDE [Date] : MAX(IF [Date]<= [Select Date] THEN [Date] END )} THEN [Category] END)}

• ###### 5. Re: MAX of one field based on another?

Okay. I used below calculation in the workbook.

{FIXED [Project ID]: MAX(IF [Date]={FIXED [Project ID]: MAX(IF [Date]<=[Select Date] THEN [Date] END )} THEN [Category] END)}