Get 2 max date in Tableau

Question

My requirement is to get the second max date available in the report and filter the data set on this.

I tried something like this:

datediff('day',dt,max(dt))=1

Referred to this link

any help?

Answer 1

• If the view has date dimension, the easy way to do this is to create a calculated Last()=1 then filter off the records that evaluate to TRUE

• You need Tableau 9.0 for this. Basically, because any calculation you do on Tableau depends on the level of detail you have on the worksheet (the dimensions you put in there). So datediff('day',dt,max(dt))=1 won't work. First, because you're mixing aggregated fields (max(dt)) with non-aggregated (dt). Second, because the aggregation depends on the dimensions in the workfield.

• But Tableau 9.0 has a new feature which is called Level of Detail calculations. It allows you to perform calculations in the level of detail you choose, depending not on the dimensions on the sheet. It  also gets calculated BEFORE any calculation on the worksheet (just after context filters).

• Now to the answer. First I'll figure out what is the max(dt). Let's call it max_dt

{ FIXED : MAX(dt) }

• This will calculate the maximum of dt in all your database

• Now to get the second max, you can go like this:

{ FIXED : MAX(IF dt != max_dt

                 THEN dt

              END)

}

• This will calculate the maximum of dt, ignoring those who are equal to max_dt (that is the true max(dt)). Therefore, the second max.

• Take a look at those LOD calculations. They were just released, I'm having tons of fun with them right now

If you want to learn more about tableau analytics then you can check our Tableau Certification Course.