5 Replies Latest reply on Jan 27, 2019 7:22 AM by Simon Runc

# Trying to calculate the minimum date for each id in a column. And format

I am trying to calculate the minimum date for a column called finishDate for each every id in another column called ID. I can write the following function MIN([finishDate]) as Calculation1 but now if I put this field into column I cannot use the DATEPART('quarter', [Calculation1]). It will not let me use a calculation like this. I want to be able to use DATEPART to be able to change from quarter to year and so on with a filter real time. Can anyone help me figure out how to show a count of one for the minimum date by id for finish times that are different by row. And then how to filter them by quarter, year, so on. THX! Really confused why the functions will not work together. my Calculation1 should have dates for each id that is the first or minimum date. and then I should be able to count the number for each quarter.

• ###### 1. Re: Trying to calculate the minimum date for each id in a column. And format

Hi Trevor,

To calculate the min date for each ID you can create an LOD.  { Fixed [ID] : MIN([finishDate] }, you can then extract a datepart from the LOD you just created.  Let me know if you have any questions.

Good luck,
Ivan

2 of 2 people found this helpful
• ###### 2. Re: Trying to calculate the minimum date for each id in a column. And format

hi Trevor,

So Tableau has several different calculation types (row level, aggregate, table calculation, LoDs). For Aggregate (as your MIN([finishDate]) is), and Table Calculation the VizLoD (Viz Level of Detail) determines their result (eg. SUM([Sales]) in superstore, if you just bring this onto the canvas it shows the value for all the Sales, if you then drag Region (adding this to the VizLoD) it now shows the SUM([Sales]) for each region. I refer to the these as 'on canvas' calculations. If you have ID in your VizLoD, then your calc works fine, but you want it at row level so you can use it 'off canvas' (i.e. as a dimension). This is why FIXED LoD were invented! they let you specify the LoD of a calculation, but return the results to every row (of the specified LoD) 'off canvas'...That the 2 min version of calcs!! (I'd highly recommend watching the On-demand calculation videos - short, free and good!)

So for your example we can create what you want with a FIXED LoD

[Min Date per ID]

{FIXED [ID]: MIN([finishDate])}

This is calculated at ID level, and returned at that level, so you can use in a non-aggregated calculation (such as DATEPART)

Hope that does the trick, and sheds some light on calculations in Tableau?

2 of 2 people found this helpful
• ###### 3. Re: Trying to calculate the minimum date for each id in a column. And format

Thank you so much both of you this was so helpful!

• ###### 4. Re: Trying to calculate the minimum date for each id in a column. And format

Hey Simon!

How would you set up the LOD expression if I want the minimum date-time of a particular day that's in the viz?

I have set up my LOD currently right now like this { FIXED [Names] : MIN([Offset Action Date]) }

but this is finding the all-time earliest date of the individual. How do I make sure that the minimum date of a filtered date is returned?

• ###### 5. Re: Trying to calculate the minimum date for each id in a column. And format

hi Shreyas,

So one of the things with FIXED LoDs are that they are computed before any regular filters are applied, so even when you filter down the date range it still shows the Global MIN date.

You can get round this by making the Date Filter a context filter (right click on filter pill and select "add to context"), this bumps the filter up the calculation pipeline.

Or you could use an INCLUDE LoD instead (which are computed later in the calculation pipeline).

Changing the filter to context is the easiest (but if performance suffers too much, let me know and I'll show you how we could do this with an INCLUDE instead, which are more efficient)