Data Viz Done Right

May 2, 2017

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

5 comments
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

    ReplyDelete
    Replies
    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.

      Delete
  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

    ReplyDelete
    Replies
    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.

      Delete