1 2 Previous Next 23 Replies Latest reply on Dec 26, 2016 10:23 AM by Sreehari Katageri

    Should I use FIXED or INCLUDE/EXCLUDE?

    Rody Zakovich

      Hello Community!

       

      Braking off a discussion started on Fixed LOD - value not "duplicating" as expected when looking by dimensions

       

      LoDs are, well just awesome!, but they can be confusing (Even for experienced Tableau users). The biggest learning curve for me has been trying to understand how/when to substitute INCLUDE/EXCLUDE with FIXED (And Vice-Versa).

       

      Now there are great resources out there (Even more that I haven't listed!)

       

      What’s new in Tableau 9.0? Part 2 – Level of Detail Expressions « Bora Beran

       

      Top 15 LOD Expressions | Tableau Software

       

      Understanding Level of Detail (LOD) Expressions | Tableau Software

       

      Level of Detail (LOD) Expressions | Drawing with Numbers

       

      But the discussion I want to start is slightly different....Hopefully this will, not only help users understand LoDs, but provide a collection of real-world examples they can relate to their current situation.

       

      1. Can you describe a situation where you needed/wanted to use a INCLUDE/EXCLUDE LoD  over a FIXED LoD (or Vice-Versa)

       

      2. Why did you decide to use one over the other?

       

      3. What steps did you take to achieve the correct calculation?


      4. How have you applied that to other situations?

       

      As always, any feedback is most appreciated!

       

      Jonathan Drummey , Mark Jackson , Matt Lutton , Simon Runc , Pooja Gandhi , Mark Fraser , Bill Lyons , Bora BeranJoe Oppelt , Nicole Edmonds

       

      Best regards!

      Rody

        • 1. Re: Should I use FIXED or INCLUDE/EXCLUDE?
          Joe Oppelt

          I don't have a lot of input here.  I just wanted to say that I'm looking forward to attending as many LOD-related breakout sessions as possible at the Conference.

          • 2. Re: Should I use FIXED or INCLUDE/EXCLUDE?
            Jonathan Drummey

            Here are a couple of links that have thoughts from me:

             

            Re: Matrix of product lift

            Re: Fixed LOD calculation - how do I exclude dates in my filter?

             

            The second one has a graphic with my currently thinking about FIXED vs. INCLUDE/EXCLUDE.

             

            With regards to #1, I view the problem differently because for me the starting place is that I've identified that I need an aggregation across the data that is at a different granularity than the current vizLOD. Depending on the goal, the data source, data structure, and data volumes, user skills, desired interactivity, etc. I might use one or more of the following techniques:

             

            a) Another Marks Card with a different LOD

            b) Reference line(s)

            c) Trend line(s)

            d) totals and subtotals

            e) self-data blend(s)

            f) computed Set

            g) table calculations

            h) custom query in the data source

            i) LOD expressions (FIXED, INCLUDE/EXCLUDE, or both).

             

            For me, a key insight is that there's a "displayLOD" that is the ultimate goal, the vizLOD that we actually use might have a very different granularit(ies) to get there.

             

            Alan Eldridge and Bethany Lyons are doing a session at TC15 on the topic of what kind of calculation to choose - see http://tc15.tableau.com/schedule/tuesday/#session-2514, they gave a similar one last year that I watched the video for and it was great, I'm planning on going to this one!

             

            Jonathan

            2 of 2 people found this helpful
            • 3. Re: Should I use FIXED or INCLUDE/EXCLUDE?
              Mark Jackson

              Simplistic answer: If you need a blue pill, use fixed. If you want to compute outside of your filters, use fixed and fine tune control with context filters. If you want a green pill that flexes with your viz LOD, use include / exclude.

              • 4. Re: Should I use FIXED or INCLUDE/EXCLUDE?
                Joshua Milligan

                Mark,

                 

                Would you please expand on your answer?  I'm not sure I'm following the logic behind it (I get the part about filters, but not understanding blue/green distinction).  I probably just need more coffee but an example or two might also help.

                 

                Joshua

                • 5. Re: Should I use FIXED or INCLUDE/EXCLUDE?
                  Rody Zakovich

                  Hey Mark,

                   

                  I don't think that "context" filters is a catch all for fine tuning a ViZ outside of the vizLoD.

                   

                  Every since Jonathan Drummey pointed me to

                   

                  Designing Efficient Workbooks – the V9.0 edition! | The Last Data Bender

                   

                  I have been very cautious of how/when I use Context filters. This is one of the biggest reason I have been trying to understand how to substitute FIXED with INCLUDE/EXCLUDE. 

                   

                  That said, I am always trying to learn. So if I have miss understood something, please let me know!

                   

                  Thanks!

                  Rody

                  • 6. Re: Should I use FIXED or INCLUDE/EXCLUDE?
                    Mark Jackson

                    I see I won't get away with the simplistic answer. =)

                     

                    The comment about context filters is irrespective of performance. I'm mostly using extracts, so it generally doesn't affect the performance of what I'm doing anyway (at least not in any noticeable way). With FIXED LOD, it ignores your filters unless they are "context". So that is what I mean by fine tuning the control of what the calculation is doing. If you need it to happen after your filter, set it to "context". If you want the LOD calc to ignore your filter, then use a standard filter.

                     

                    The blue / green distinction is a matter of whether you need a discrete dimension or not. I know I'm conflating terms here with blue/green (dimension/measure) and aggregate/non-aggregate (continuous/discrete). Using INCLUDE/EXCLUDE will place the pill in the measure section of Tableau. Try using that as a discrete dimension and you'll see the problem. So that is why I said to use fixed when you need a blue pill...and by blue pill I mean discrete dimension.

                     

                    You can use fixed for green pills as well, but it won't flex with changes to your viz. There are nuances to all of this, but this is my rule of thumb.

                    2 of 2 people found this helpful
                    • 7. Re: Should I use FIXED or INCLUDE/EXCLUDE?
                      Simon Runc

                      hi Joshua,

                       

                      If my understanding is correct, Mark is referring to Dimensions over Measures here (in his Blue/Green definition...better hope Joe never see's this thread!!).

                       

                      FIXED calcs are essentially Row Level calcs (or probably more accurately run over every row), and so can be used as 'Genuine' dimensions, but as the INCLUDE/EXCLUDE are aggregate calculations, and so are reliant on the Viz Level of Detail and thus can't be used as 'Genuine' dimension (eg. Can be used to Blend, Run Row Level IF Statements). I use the qualification of 'Genuine' as you can make any measure discrete (Blue), and use them as a psudo-dimension.

                       

                      I think Jonathan's approach to this, both his decision matrix and thought-flow above is very enlightening. I, and I'm guessing many other, think (or did before this!) about the calculation I want to perform, and then try and work out the 'best' method. Jonathan's subtle difference is to consider the Viz you are building first (i.e. you have the Level of Viz detail in mind, and it's 'behavior', in terms of filters/actions...etc.) and use that as the starting point...it seems such an obvious approach when you think about it, and actually makes the decision on what to use more straight-forward/simple. I think this 'bias' (certainly on my part) comes from a data-base/Excel background where you are always concerned with the calculation, and how you will use it later. In short 'Plan your Viz, and then design the calculations to make that Viz'.

                      3 of 3 people found this helpful
                      • 8. Re: Should I use FIXED or INCLUDE/EXCLUDE?
                        Alexander Mou

                        My simplistic answer:

                         

                        Fixed: absolute dimensions (calculation regardless of dimensions in view)

                        Include/Exclude: relative dimensions (calculation relative to dimensions in view)

                         

                        Bora in his article includes a chart on the precedence of filters which I found very useful in determining when to use what.

                         

                        Filters in Tableau

                        1 of 1 people found this helpful
                        • 9. Re: Should I use FIXED or INCLUDE/EXCLUDE?
                          Jonathan Drummey

                          @Simon - a couple of notes on what you wrote:  INCLUDE & EXCLUDE LOD expressions *can* be evaluated as record-level results in IF statements, they only have to be aggregated when placed as pills in the view. I first learned about this when going through the top 15 at https://www.tableau.com/LOD-expressions, there's an example in #8, I've used this a few times since, here's another example at LOD Expression Remix – Finding a Dimension at a Lower Level | Drawing with Numbers.

                           

                          In terms of approach, I actually don't think about the vizLOD in the beginning. When I'm going from an exploratory mode into an engineering mode (i.e. where I have a specific goal in mind instead of just dragging and dropping pills) then my first thought is about the final view (what I think of as the displayLOD) and my next thoughts are about the structure of the data, asking how well it lines up with the view. What actually ends up as dimensions and measures in the vizLOD is a byproduct of the process to get from the data to the desired viz. The first time I articulated this way of thinking was in Eddie Van Halen and Dashed Lines | Drawing with Numbers, I've been refining that in a number of presentations since (including a TDT or two and last year's TC14 talk). The book research and writing that I've been doing for the last couple of years has been focused on identifying and articulating the key skills and processes that are required to work with Tableau in this way, where we treat Tableau as an empty canvas rather than as a more limited tool with a number of switches and knobs.

                           

                          Jonathan

                          2 of 2 people found this helpful
                          • 10. Re: Should I use FIXED or INCLUDE/EXCLUDE?
                            Simon Runc

                            Thanks for correcting me (...and very good to know), and would advise anyone (who hasn't already) to have a look at

                            LOD Expression Remix – Finding a Dimension at a Lower Level | Drawing with Numbers

                            That's a great example to show what's going on with these. I had really only used INCLUDE/EXCLUDE to get the calculation to work dynamically under filtering, but hadn't really played with it's behavior outside the VizLoD (and the trick of not specifying a dimension).

                             

                            Yes that was my understanding of your approach, but didn't appreciate the subtle difference between VizLoD and DisplayLoD...which is an important distinction to make.

                             

                            ...learned a huge amount already!!. Great discussion.

                            • 11. Re: Should I use FIXED or INCLUDE/EXCLUDE?
                              Jonathan Drummey

                              I was thinking more about this thread on the bus home and wanted to add a few points:

                               

                              - If you want the LOD calc to be responsive to dragging & dropping pills then it has to be an INCLUDE or EXCLUDE. However, this doesn't always work the way I want, sometimes I want an EXCLUDE * option, that's why I created this feature request: http://community.tableau.com/ideas/4492.

                               

                              - Nested INCLUDE & EXCLUDE calcs that are computing at different granularities are harder to build & verify than FIXED because you have to work out all the dimensions that are in play for each nested calc. Sometimes you have to exclude a dimension at one level of nesting and the include it in a child level, the simplest example I can come up with is the Avg of the Sum of Sales per Region. This is WINDOW_AVG(SUM([Sales])) with a Compute Using on Region, as a relative LOD it is ATTR({EXCLUDE [Region] : AVG({INCLUDE [Region] : SUM([Sales])})}).


                              Screen Shot 2015-08-18 at 6.27.24 PM.png

                               

                              - Trying to duplicate table calcs in LOD expressions is a nice way to learn more about LODs.


                              - Experience writing aggregate SQL queries is a huge advantage in writing LOD expressions, because in both cases we have to do a lot of abstract thinking at different levels of granularity and how LOD expression joins to others (in nesting) or the vizLOD.


                              Jonathan



                              2 of 2 people found this helpful
                              • 12. Re: Should I use FIXED or INCLUDE/EXCLUDE?
                                Rody Zakovich

                                Hi Jonathan,

                                 

                                Thank you for adding this to the discussion!

                                 

                                When I first started with LoDs, I could understand why { EXCLUDE : SUM([Value]) }  didn't "exclude" all of the dimensions in my vizLoD.

                                 

                                To be honest, I'm still not 100% on the differences (If any) between { EXCLUDE : SUM([Value]) } and { INCLUDE : SUM([Value]) } . I've been playing around with them in various situations, including nested include/exclude, but I can't really see any differences between the two. Hopefully you can clear up my confusion

                                 

                                Thanks again for everyone's contribution to this discussion!

                                 

                                Rody

                                • 13. Re: Should I use FIXED or INCLUDE/EXCLUDE?
                                  Nicole Edmonds

                                  Agreed on the Exclude statement, as with FIXED when you don't specify the dimensions it assumes all, so I also would also assume all 3 LODs would function similarly.  This thread that I started has been very educational.  One of the other things I have done as a result of wanting to understand the behaviors better, is step through each step of the calc to get to my end result to understand at each level what it is doing. 

                                   

                                  Top 15 LODs was very usual for me, but when I get into the meat of the scenarios I am trying to address with my data, and ultimately what my end users are consuming, it's a bit trickier to ensure data quality and accuracy. 

                                  • 14. Re: Should I use FIXED or INCLUDE/EXCLUDE?
                                    Jonathan Drummey

                                    @Rody - So far as I've been able to see, {INCLUDE : ...} and {EXCLUDE : ...} function the same.

                                     

                                    @Nicole - Here's how I've internalized the difference in behavior when there is no dimension in the declaration:

                                     

                                    FIXED is truly a "fixed" LOD. So when I don't include any dimensions in the declaration then I'm telling it to process over the entire data set.

                                     

                                    INCLUDE & EXCLUDE are "relative" LODs, in particular they are relative to the vizLOD so their dimension declaration effectively includes the vizLOD. In other words, {INCLUDE : ...} is specifying "include all dimensions from the vizLOD and don't include any more" and {EXCLUDE : ...} is specifying"include all dimensions from the vizLOD and don't exclude any".

                                     

                                    Jonathan

                                    3 of 3 people found this helpful
                                    1 2 Previous Next