
1. Re: How this LOD expression works ?
Simon Runc Aug 18, 2018 10:35 AM (in response to Ombir Rathee)2 of 2 people found this helpfulhi Ombir,
So that's a very good question (and it's actually quite sneaky!)...
Let's start with sales calculation formula...
If SUM({ EXCLUDE [State],[City] : COUNTD([State])})=SUM({COUNTD([State])})
THEN SUM({ FIXED [State] : SUM([Sales])})
ELSE SUM([Sales])
END
First the Level of Detail of the Viz is State/City (so there is a mark for each state/city...more on this later)
In the below table I have detailed out (in a table each of the calculations), when all states are selected. As you can see the EXCLUDE LoD is a count of all states (48), and the FIXED LoD is also a count of all states (48)
As we have City/State in the VizLoD, the EXCLUDE LoD, Excludes (unsurprisingly!) these 2 levels and does the COUNTD over the dataset (48). The FIXED LoD does the same (as it has no FIXED Level it's a COUNTD of all states). Now due to the "Order of Operations" in Tableau, an EXCLUDE LoD is respondent to (regular) filters, where as FIXED are computed before any (Regular) filters are applied. So when we select a state, which is what the action does, we get this
So this is how the first part of the calculation determines if a state has been selected or not, and returns ether the Single Value for a state or the City values (as the VizLoD is State/City). I mention this as the other formula follows similar logic.
So for the Show Cities formula
IF MAX({FIXED [State] : MAX({FIXED [State],[City] : SUM([Sales])})})=SUM([Sales])
AND
SUM({ EXCLUDE [State],[City] : COUNTD([State])})=SUM({COUNTD([State])})
THEN TRUE
ELSEIF SUM({ EXCLUDE [State],[City] : COUNTD([State])})<>SUM({COUNTD([State])})
THEN TRUE
ELSE FALSE
END
If we do a similar thing with everything selected, we get this
So when all states are selected, Andy doesn't want to return a mark for every City/State (even though every city in a state would have the same position), as they would overlap and make it a bit messy. So he determines only the Max Sales City, and marks that one TRUE (so the mark you are seeing is actually one city in the state...it just happens, via an LoD to be the addition of every city in that state). The label is used in a similar way to know if the label should show the city or the state name.
When we select a single state....
As the EXCLUDE is respondent to (regular) filtering, every city gets marked TRUE.
In his Viz, he then brings this field onto the Colour Shelf, and Hides all the FALSE values (as he is hiding the value FALSE, and not the actual cities, it is dynamic and responds to the filtering). If you go to his Viz and select Reveal Hidden Data, with no state selected, you'll see the mark count go from 48 to 1616
Once he had hidden the FALSE, he then removes the Show Cities from the colour (and just leaves it in the level of detail)
Hope that helps explain how this trick is done (it took me a while to work out how he'd done it)...very clever (and a bit sneaky with the use of hide!)

2. Re: How this LOD expression works ?
Ombir Rathee Aug 19, 2018 1:01 AM (in response to Simon Runc)Thanks a lot Simon for explaining in a simpler way. I gave up on this after spending a day and reached out to forum for help and as usual I'm not disappointed. Thanks again.
One last question: I was experimenting with {FIXED [State],[City] : MAX([Sales])} but this returns odd results. Could you please explain how this expression is working ?

3. Re: How this LOD expression works ?
Simon Runc Aug 20, 2018 1:04 AM (in response to Ombir Rathee)1 of 1 people found this helpfulGlad it made sense.
So with regards your {FIXED [State],[City] : MAX([Sales])} question.
So what this is doing is using the level of detail of State and City and returning the MAX sales (row) for that level. It's like dragging State and City into the Rows Shelf and adding Max of Sales as the measure
The Data Level of Detail is Order/Item (I think of this as "what does each row of the data represent?"), so it returns the MAX value for each State/City
In Andy solution, he first SUMs up the Sales by State/City and then takes the MAX, for each state (by nesting the FIXED LoDs)
{FIXED [State]: MAX({FIXED [State], [City]: SUM([Sales])})}
Hope that helps.