3 Replies Latest reply on Aug 20, 2018 1:04 AM by Simon Runc

# How this LOD expression works ?

Hi friends,

I am trying to replicate the view from below link. Its a single sheet Scatter plot drill down by Andy Kriebel.

http://www.vizwiz.com/2017/10/drill-down.html

The whole logic of this view seems to based on below two calculation, which is named as 'Show Cities' and 'Labels' Calculation in the workbook.

Show Cities:

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

Labels:

If SUM({ EXCLUDE [State],[City] : COUNTD([State])})=SUM({COUNTD([State])})
THEN
IF [Max State Sales]=SUM([Sales])
THEN ATTR([State])
END
ELSE ATTR([City])
END

I am having hard time to understand how this calculation works. I would be really grateful if anybody help me to understand the above calculation in layman terms preferably with the help of text table (Step by Step approach)

Regards,

Ombir Rathee

http://www.vizwiz.com/2017/10/drill-down.html

• ###### 1. Re: How this LOD expression works ?

hi Ombir,

So that's a very good question (and it's actually quite sneaky!)...

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 of 2 people found this helpful
• ###### 2. Re: How this LOD expression works ?

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 ?

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.

1 of 1 people found this helpful