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

    How this LOD expression works ?

    Ombir Rathee

      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 ?
          Simon Runc

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

            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

              Glad 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.

              1 of 1 people found this helpful