Data Viz Done Right

# Tableau Tip Tuesday: How to Highlight the Min & Max Across Months with a Nested LOD

This week's tip is a nice use case for nested level of detail expressions and also a nice test for my "LODs in an English sentence" method. It started with this tweet from Charlie Hutcheson:

If you scroll through the comments, you'll see that Charlie was hoping to do this via LOD, but couldn't quite get it. My tip this week solves this problem.

Enjoy!

#### 5 comments :

1. Hi Andy
Given that in your [Min or Max] calculation the outcome is the same in both cases (i.e. sum([Trips]) ) would it be more/less efficient to use an OR statement rather than the ELSEIF?

For example:
IF
SUM({FIXED [Route] : MAX({FIXED [Route], DATETRUNCE('month',[Date]) :SUM([Trips]) })}) = SUM([Trips])
OR
SUM({FIXED [Route] : MIN({FIXED [Route], DATETRUNCE('month',[Date]) :SUM([Trips]) })}) = SUM([Trips])
THEN SUM ([Trips])
END

Thanks

Andy

1. Yes that works too. As for efficiency, it all depends on how Tableau writes the sql query. The difference would only be seen on massive data sources anyway.

2. Thanks a lot

2. Good morning Andy,

it works great with me. The only thing I couldn't figure out is how I could achieve the same Min/max behaviour if there is a dnyamic view on the month. I.e.: Searching the Min/Max if only Sep'16 to Jan'17 are filtered. I tried it with include instead of fixed. But this was misleading.

What might be the solution? Something with Window...?

Thank's for your help again.

Cheers
Holger

1. The problem you're running into there is that a FIXED LOD will ignore the dimension filters. One solution is to make the Date field a Context filter, as these get applied BEFORE a FIXED expression.