CODEWITHSUNDEEP
tableau-api
vhadalgi
vhadalgi

Reputation: 7189

Get 2 max date in Tableau

My requirement is to get the second max date available in 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?

Upvotes: 2

Views: 8266

Answers (2)

vhadalgi
vhadalgi

Reputation: 7189

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

Upvotes: 0

Inox
Inox

Reputation: 2275

You're going to 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-aggreagated (dt). Second, because the aggregation depends on the dimensions in the workfield.

But Tableau 9.0 has a new awesome feature, 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 is also 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 calculated 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 calculated 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 on those LOD calculations. They were just released, I'm having tons of fun with them right now

Upvotes: 4

Related Questions