7 Replies Latest reply on Nov 12, 2018 11:03 PM by Larsen Rennie

    LOD vs Table Calculation

    Larsen Rennie

      Hello

       

      I was working with Sample Super Store Data and i created one simple view by putting Category and then Subcategory on the Row shelf and then  for the measure i created a calculation which is basically the average of the median sales .  i was trying to write the same calculation with the LOD but result of my LOD Calc is not coming same as Table Calculation

       

      For Table Calc i written formula as WINDOW_AVG(MEDIAN([Sales])). I am computing it using Subcategory

       

      and Using LOD i am writing same calculation as AVG({ Fixed [Sub Category]:MEDIAN([Sales])})

       

      But my result is not coming same as Table calculation  My intention is to write the same table calculation using LOD but i am not able to achieve the result . Can anyone please guide me if i am using correct function in the LOD or how can i achieve it using correct lod

       

      I am attaching the twbx file for your reference and please find below the screenshot for your refrence . As you can see below the lod which i written is giving 182 as average for furniture instead of 290 which is coming from Table Calculation and which is correct also if you will add 307,362,4,448 and divide it by 4 which will give 290

       

      Jonathan Drummey

        • 1. Re: LOD vs Table Calculation
          Deepak Rai

          {AVG({ Fixed [Sub Category]:MEDIAN([Sales])})}

          • 2. Re: LOD vs Table Calculation
            Larsen Rennie

            Hi Deepak

             

            i tried with above expression which you mentioned but its giving me 206 which is incorrect.

            • 3. Re: LOD vs Table Calculation
              Deepak Rai

              ok let me recreate something

              • 4. Re: LOD vs Table Calculation
                Deepak Rai

                Thanks

                Deepak

                If it Helps, Pl Mark it Helpful and CORRECT to close Thread

                1 of 1 people found this helpful
                • 5. Re: LOD vs Table Calculation
                  Jonathan Drummey

                  Hi Larsen,

                   

                  When you're working with Superstore data please be sure to attach the packaged workbook, that helps us make sure we're using the same version as you. The behavior that you're seeing in the AVG of the FIXED LOD is no longer current behavior for Tableau (there were edge cases when LODs were introduced in v9.0 that were slowly fixed over later versions).

                   

                  In any case what the AVG([FIXED LOD]) will do is compute the average *in the current context* which given the vizLOD of Category & Sub-Category means that the average of the fixed LOD will be the average for each Sub-Category. Here's a view built with v9.3:

                   

                  Screen Shot 2018-11-12 at 6.13.31 PM.png

                   

                  Now here's what happens when I remove Sub-Category from the vizLOD (I dropped the Window Avg table calc because it won't work in this view, since Sub-Category is no longer in the vizLOD but Category is the average of the FIXED calc is the correct average of the sub-category level median sales:

                   

                  Screen Shot 2018-11-12 at 6.13.52 PM.png

                   

                  There's an extra calculation that I added to both views, that's the Exc/Inc Avg of Median Sales, here's the formula:

                   

                  {EXCLUDE [Sub-Category]: AVG({INCLUDE [Sub-Category] : MEDIAN([Sales])})}

                   

                  The inner INCLUDE is getting the median sales per sub-category, then the outer EXCLUDE computes the average across sub-categories (by excluding the Sub-Category dimension), and Tableau adds the default ATTR() aggregation for EXCLUDE LOD expressions.

                   

                   

                  This calculation is the only one that works at either vizLOD, the other calcs will work at one vizLOD but not the other.

                   

                  Note that we could write this using FIXED LOD expressions with the following formula:

                   

                  {FIXED [Category] ; AVG({FIXED [Sub-Category]: MEDIAN([Sales])})}

                   

                  This formulation has two limitations: First of all it's not responsive to other dimensions in the view. For example if we brought year as a dimension into the view then we'd have to edit the calculation. Secondly if we want filters to affect the fixed results then we have to remember to add them as context filters. The Exc/Inc version has neither limitation, so it's my preferred method if I want do replicate a WINDOW_ function in an LOD expression.

                   

                  v9.3 workbook is attached.

                   

                  Jonathan

                  • 6. Re: LOD vs Table Calculation
                    Larsen Rennie

                    Hi Deepak

                     

                    The solution which you provided worked for me as well as Jonathon Solution . Thank you to both of you for helping me out with pulling this calculation. I really appreciate Jonathon explanation on how we can write the efficient solution for lod without altering the view and without the use of context filter

                     

                    Thank you all

                    • 7. Re: LOD vs Table Calculation
                      Larsen Rennie

                      Hi Jonathon

                       

                      The solution which you provided is amazing . I have one doubt which i want to clear from you. Lets say i have created a group out of the Category column  because i want to group certain categories which is the scenario with my real project and then if i use that group instead of Category on the row shelf  will the calculation u wrote above be applicable for that also or do i need to do anything else. If you can pull an example of this scenario using the Group that will be great

                       

                       

                      Thanks

                      Larsen