1 2 Previous Next 26 Replies Latest reply on May 17, 2018 5:36 AM by Dimitri Shvorob Go to original post Branched to a new discussion.
      • 15. Re: Should I use FIXED or INCLUDE/EXCLUDE?
        Bora Beran

        {include : sum(value)} (same for the exclude counterpart)  includes/excludes nothing so you get same as viz LOD. It is essentially a workaround for doing sales/sum(sales) without getting the mixing aggregates and disaggregates error. If and when we support writing sales/sum(sales), then they will essentially be obsolete.

         

        We intend to add something that is similar to fixed (in the way it allows naming the dimensions you want explicitly or naming nothing to get a single value for the whole table) that acknowledge the filtering that happen after context filters that should also address the EXCLUDE * scenario.

         

        Also we have been working on a lot of performance optimizations for LOD calculations so stay tuned

        3 of 3 people found this helpful
        • 16. Re: Should I use FIXED or INCLUDE/EXCLUDE?
          Sreehari Katageri

          Hi,

           

          Am finding it difficult to understand the difference between FIXED vs INCLUDE vs EXCLUDE.
          I understand the concept of FIXED. It is something like Group By/Partition By in the SQL. But how it is different from the INCLUDE/EXCLUDE.

           

          I tried to understand the concept by using the super store data set, but I couldn't. How can i differentiate among these with an example in the same view.

          {FIXED Sub-Category : SUM(Sales)}

          {INCLUDE Sub-Category : SUM(Sales)}

          {EXCLUDE Sub-Category : SUM(Sales)}

          SUM(Sales)

           

          When I take Sub-Category on Rows and FIXED/INCLUDE/SUM(Sales) over the Measure values, I see FIXED,INCLUDE and SUM(Sales) shows the same result and EXCLUDE shows different result.

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

            Hello Sreehari,

             

            It all comes down to the vizLoD (Visualization Level of Detail). You can think of this at a high level as "The various pills you have in your viz that affect the "Group By and/or Where" clause of a Query " Include/Exclude work in combination with these pills.

             

            Have a look at Bora's write up on the topic of LoDs and differences between Fixed, Include and Exclude

             

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

             

            And also Check out Jonathan Drummey Blog. He has a lot of great write ups on the topic.

             

            lod expression | Drawing with Numbers

             

            Once you have that down, you can check out this response from Jonathan Drummey as well that talks about when to use LoDs vs Table Cal cs

             

            Re: LOD

             

            If you run into any issues, please feel free to ping me.

             

            Regards,

            Rody

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

              Hi,

               

              My first suggestion is to read all the links that Rody sent, there's some

              great stuff there (and it's not just because I wrote some.

               

              My second suggestion is really about setting up your mental model. Tableau

              works at multiple levels of granularity/detail, here are the 3 that are of

              interest right here:

               

              - record-level/raw data - what's in each record, as well as the results of

              record-level calculations

              - Level of Detail expression aka LOD expression

              - viz Level of Detail aka vizLOD - what's created by the dimension(s) in

              the view or a particular Marks Card where measures are (typically)

              aggregated to that level of detail

               

              So in the screenshot you posted we are seeing the results of those 3

              different levels of granularity expressed at the vizLOD and therefore some

              of what is happening (like why the FIXED and INCLUDE are returning the same

              results) is hidden from your view.

               

              In order to reveal Tableau's internal logic we need to a) read the docs

              (i.e. Rody's links), and/or b) create views that better express what

              Tableau is doing.Here's a post I wrote where I walked through my process on

              validating LODs that goes through how to build those views:

              http://drawingwithnumbers.artisart.org/lod-expression-remix-finding-a-dimension-at-a-lower-level/

               

              Jonathan

               

               

               

              On Tue, Mar 29, 2016 at 8:52 AM, Rody Zakovich <tableaucommunity@tableau.com

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

                Thanks Jonathan Drummey for jumping in!

                 

                Great comment about setting up a Mental Model for Tableau. Such an important part of learning/understanding some of the complexities in Tableau (Though not everyone's metal model is the same, having one is highly beneficial).

                 

                Best regards,

                Rody

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

                  Hi all, Hi Rody Zakovich,

                   

                  I red the many answers that people made to your requests, and that was really interesting.

                   

                  I am facing right now the issue that you expressed in your very first message, that was :

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

                   

                  I just still don't know if this could be possible, but I also really think that it would make sense in my situation. A situation where I need both to "fix" and exclude dimensions.

                   

                  So I was hoping that you'll willingly give me some imputs more than one year later

                   

                  Many thanks,

                  Jonathan (2)

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

                    Hi Jonathan,

                     

                    Are you still looking for help with this?

                     

                    Jonathan

                    1 of 1 people found this helpful
                    • 22. Re: Should I use FIXED or INCLUDE/EXCLUDE?
                      Sreehari Katageri

                      Hello Rody and others,

                       

                      Sorry for the delay in response. Based on all your inputs I understood and framed the explanation on LOD with my own words. Please correct me if am wrong anywhere and I hope this makes it simpler to understand for others who are still struggling to know what is LOD.

                       

                      When we are playing with the data in our daily life we always end up aggregating the data at different level based on our requirement. For example if we consider the Sales Order data; data set includes fields Order Id, Customer Name, Region, Category, Sub category, Sales. The lowest granular level is the Order Id. We may need data aggregated at Category level or Region level or on both the fields. In this case on the visualization we see data is aggregated based on the dimensions we consider as shown below.

                      1.jpg

                       

                      We should always note that the granularity of the visualization changes based on the numbers of dimensions added and removed. More and more dimensions are added on the visualization the granularity of information increases with decrease in summarized information. For example in the above  aggregated information, if I go on add all the fields available then the view is as good as the actual table itself. 

                       

                      Now suppose if I need Avg Sales in each Category, Avg Sales per Region in each category, Percentage of Sales in each category. In this scenario we do not need all the dimensions on the view, we just need Category in the visualization and the corresponding values. To achieve this we use LOD (Level Of Detail). In the view we use only one dimension by the aggregation is done at different levels based on the LOD functions.

                       

                      Avg Sales in each Category:

                      1.jpg

                      Avg Sales per Region in each category

                      1.jpg

                      Percentage of Sales in each category

                      1.jpg

                      There are 3 types of LOD:

                       

                      • FIXED
                      • INCLUDE
                      • EXCLUDE

                       

                      FIXED :

                      This is similar to PARTION BY aggregate function in SQL.  The below query will give the Total Sales of all Category, which repeats in all the rows at the detail level.

                       

                      SELECT *,Total=SUM([Sales]) OVER (PARTITION BY 1)

                      FROM [dbo].[SalesOrderTableu]

                      1.jpg

                      Below is the equivalent LOD function. This is called as FIXED since whatever dimension is used in the visualization the SUM of Sales is FIXED for the dimension used in the LOD only. In the below example no dimension is used, which means the over all sum of sales. So it repeats 893 at the lowest and highest granular level of the data.

                       

                      { FIXED : SUM( [Sales] ) }

                      1.jpg

                      1.jpg

                      If we need to use dimension in LOD then the function would be like below

                      { FIXED [Category] : SUM( [Sales] ) }

                       

                       

                      INCLUDE :

                      This is similar to GROUP BY aggregate function in SQL. In this case the result is not just FIXED only based on the dimension used in LOD, but the result will also vary based on the dimension used in the visualization.

                       

                      Suppose if we need Avg Sales per Region in each Category then below LOD is used. Here first we get the sum of sales based on Region and then take the average of all the region.

                       

                      AVG( { INCLUDE [Region] : SUM( [Sales] ) } )

                      1.jpg

                      (150+210+240+180)/4 = 195

                      (20+18+7+23)/4 = 17

                      (10+15=8+12)/4 = 11.3

                      1.jpg

                      Equivalent SQL query is below

                      SELECT Category, AVG(Total)

                      FROM

                      (

                             SELECT Category, Region,SUM([Sales]) Total

                             FROM [dbo].[SalesOrderTableu]

                             GROUP BY Category, Region

                      )x

                      GROUP BY Category

                      1.jpg

                       

                      EXCLUDE :

                      This is just opposite to INCLUDE. It will EXCLUDE the dimension mentioned in the LOD. It will group by the dimension other than the dimension used in the LOD. For example below LOD will never show aggregation at the dimension Region.

                       

                      { EXCLUDE [Region] : SUM( [Sales] ) }

                      1.jpg

                      If you notice above pic, it is equivalent to SUM( [Sales] ). In the visualization we have used Category, so it will aggregate based on Category.

                      Now if you notice above pic, we have included Region is the visualization, but the aggregation is still at the Category level.

                      1.jpg

                      Now let us add Sub-Category to the visualization and you see the aggregation is at the Sub-Category level with in each Category.

                      1.jpg

                      So from our initial requirement below are the LOD function and the visualization

                       

                      Avg. Sales in each Category:  AVG(SUM([Sales]))

                      Avg Sales per Region in each category: AVG( {INCLUDE [Region]:SUM([Sales])} )

                      Percentage of Sales in each category: AGG ( SUM([Sales])/ATTR({FIXED :SUM([Sales])}) )

                      1.jpg

                      2 of 2 people found this helpful
                      • 23. Re: Should I use FIXED or INCLUDE/EXCLUDE?
                        Sreehari Katageri

                        Hi Rody,

                         

                        Please let me know if my understanding about LOD in this post is correct or wrong?

                        1 of 1 people found this helpful
                        • 24. Re: Should I use FIXED or INCLUDE/EXCLUDE?
                          praveen p

                          Thank you all for the explanation and provided links

                          • 25. Re: Should I use FIXED or INCLUDE/EXCLUDE?
                            Natasha John Williams

                            Hi Sreehari,

                             

                            Thank you for your post. I tried to build similar views myself and I am getting the ATTR problem. I am getting a * for a Column Grand Total when using EXCLUDE. Here I am excluding the Region and trying to get the Sales.

                            My calculation is { EXCLUDE [Region] : SUM( [Sales] ) }

                             

                            Please have a look at the screenshot and advice me. Screen Shot 2017-10-30 at 3.47.25 pm.png

                             

                            Thanks again.

                             

                            Cheers

                            Natasha

                            • 26. Re: Should I use FIXED or INCLUDE/EXCLUDE?
                              Dimitri Shvorob

                              Let me offer my two cents. I will ignore INCLUDE, and focus on "FIXED vs. EXCLUDE".

                               

                              1. I have not yet grasped a unified theory of Tableau LOD expressions, and go with heuristics. I see 2 principles, and 2 scenarios.

                               

                              2. The principles:

                               

                              P1. FIXED runs before dimension filters are applied, EXCLUDE runs after. (Context filters and even more upstream filters apply in both cases).

                              P2. FIXED involves a preliminary aggregation to produce a lookup table. (EXCLUDE does not).

                               

                              3. (P1 is dead simple, but let's elaborate on P2). With FIXED, ignore everything on your sheet except for (a) context filters, (b) parameter values affecting calculated fields. Do three things.

                              • Apply the context filters to the data table.
                              • Calculate the aggregation - i.e something like "select category, sum(sales) as sales from data group by category" - and get a lookup table. (Category => sales).
                              • As far as future use of this LOD is concerned, forget the original table, retain only the lookup table.

                               

                              4. The scenarios: "percent of total" (PT) and "difference from" (DF). (See Examples 4 and 6 of "Top 15 LOD Expressions" by Bethany Lyons).

                               

                              5. Dimension filters come into play with PT scenario. A dimension filter drives apart the "real" total and the visible total, i.e. the total excluding filtered-out dimension members. The visible total changes as you filter in/out dimension members.

                               

                              6. With PT, use FIXED if you are after ("fixed") percentages of the "real" total, use EXCLUDE if you are after ("floating") percentages of the visible total. The explanation is simply P1.

                               

                              7. With DF, use EXCLUDE. FIXED will not work. Try Bethany Lyons's Example 6, switch to FIXED and see what happens. Understand why "Sales of Selected Category" is zero for unselected categories (easy), and why the second "sum" in the expression for "Difference From Selected" can be replaced with "min" or "max". (Hint: how many records per category are there in the lookup table?)

                              1 2 Previous Next