May 2, 2017
Tableau Tip Tuesday: How to Highlight the Min & Max Across Months with a Nested LOD
Charlie Hutcheson
,
fixed
,
highlight
,
indicator
,
level of detail
,
LOD
,
max
,
min
,
nested
,
Tableau Tip Tuesday
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:Tiny #MakeoverMonday viz this week. One of my best mates goes on the Manly route as his daily commute. Lucky guy! https://t.co/WnxLuTYi1A pic.twitter.com/G3eBOCbg1F— Charlie Hutcheson (@CharlieHTableau) May 1, 2017
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!
Subscribe to:
Post Comments
(
Atom
)
Hi Andy
ReplyDeleteGiven 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
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.
DeleteThanks a lot
DeleteGood morning Andy,
ReplyDeleteit 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
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