7 Replies Latest reply on May 29, 2018 10:24 AM by Meghan Kyle

# Return a value for Min Date in View

Hi,

I want to create a calculated field that returns the value for the min date in view.  I want to then use this field in other custom calculations that have been requested by the business.

Example:

 Balance FY16Q1 58,000 FY16Q2 20,000 FY16Q3 70,000 FY16Q4 5,000 FY17Q1 12,000

How do I create a calculated field that will figure out 58,000 is the value I want?

Goal - Calculated Field:

 Dimension Calculated Field FY16Q1 58,000 58,000 FY16Q2 20,000 58,000 FY16Q3 70,000 58,000 FY16Q4 5,000 58,000 FY17Q1 12,000 58,000

Thanks!

• ###### 1. Re: Return a value for Min Date in View

Hi Meghan! You'll want to use a LOD calculation for this. First, you'll want to find the minimum date in the context. Let's call this calculated field MinDate.

{ MIN([Date]) }

Then, you'll use an IF statement to pick out the right value.

{ SUM(IIF([Date] = [MinDate], [Balance], NULL)) }

If you have Tableau 10+, you can merge the two together by using FIXED with the first part of the IF statement.

• ###### 2. Re: Return a value for Min Date in View

Thanks David! This worked perfectly.  Appreciate your help :-)

• ###### 3. Re: Return a value for Min Date in View

You're welcome!

• ###### 4. Re: Return a value for Min Date in View

Hi everyone,

Very similar issue with slight difference = I need to return a string value based on the earliest date. So for UniqueID below, 123  would return a string value of mushroom for the variable String.

 UniqueID Date String 123 1/1/2018 apple 123 1/2/2018 green 123 4/3/2018 cat 123 3/17/2017 mushroom
• ###### 5. Re: Return a value for Min Date in View

Hi Jenni!

Not sure if this will work in the way you need it to but I would do the follow:

(1) Create a calculation that determines the minimum date in your data

(2) Create a second calculation, this time a true-false value, that looks for the row in your data that matches the calculated minimum date

(3) Create view and set your calculation from step #2 to true

I've attached the workbook.

Hope that helps!

• ###### 6. Re: Return a value for Min Date in View

Ah, the problem is I have multiple cases represented by UniqueID, and when I did the mindate function it set every record to it's own date :-/

So for each uniqueID i only want to return the string value for the case with the earliest date. I attached a workbook example.

Thank you!

• ###### 7. Re: Return a value for Min Date in View

So this is where you need to extend your use of Level of Detail calculations.  In my previous reply my step 1 was to fix the min date to the entire view. Hence FIXED : MIN (Date).  What you actually want to return is the Minimum Date for each Unique ID. That translates in Tableau speak to:  FIXED Unique ID: MIN(Date)

I updated your workbook with the new calculation from my step 1, all other steps remain the same:

1 of 1 people found this helpful