6 Replies Latest reply on Jan 17, 2019 3:18 AM by Jim Dehner

    understanding LOD - By replicating results in excel

    Vikash Kumar

      Hi All,


      I am trying to gain a better understanding of LODs and trying to do the same by replicating the results produced by LOD calculations into my excel sheet via. pivot using the Sample Superstore data.


      I understand that the INCLUDE and EXCLUDE LODs are affected by the view level detail which can be present in the Columns shelf, Rows shelf or the Details button.

      Also that using a dimension in the INCLUDE calculation which is already present in the view level detail will have not effect. Similar is the case when we try to EXCLUDE a dimension which is not part of the view level detail.


      However, i am unable to understand the FIXED LOD properly specially in the case when the dimension used in the calculation is not matching with any of the dimensions in the view level detail.


      Can someone please explain me on how the results are displayed in the worksheet for the FIXED LOD calculation with the sub-category on the rows shelf.


      THE FIXED LOD used is {FIXED [State]: SUM([Sales])}. Already have this calculation with the name "FIXED State". Name of the worksheet is "Doubt".

      Also, when i checked for the sub-category "Accessories" and viewed the data, i see values populated under the column "Fixed State" which is the name of the LOD calculation which i am unable to make any sense of though i tried making pivots and did some slicing and dicing to understand the same. The same data is attached with the filename as "Doubts.csv.xlsx".


      I am just trying to practice with different combinations for better understanding of the LODs. There may/may not be a business sense arising out of what i am trying to do.


      I have attached the tableau workbook for reference.


      Thanks a lot.



      Apologies for attaching the wrong file. Attached the correct one.


      Message was edited by: Vikash Kumar

        • 1. Re: understanding LOD - By replicating results in excel
          Zhouyi Zhang

          Hi, Vikash


          It seems you attached wrong workbook. there is only 1 worksheet as shown below



          • 2. Re: understanding LOD - By replicating results in excel
            Jim Dehner

            Good morning

            Fixed is relatively straight forward - think of the format as {fixed [dim1], [dim2] ... [dimn] : aggregating expression }


            What LOD's do is create another virtual layer in the dat that is at a higher level than the level of the base data in the viz - they must be aggregate but ar not aggregates in themselves - they can be used in other calculations and LODs or table calcs - so much for the basics


            for FIxed in words you would read it as   for each combination of the dimensions that precede to colon (:) create an aggregate based on the expression that follows the colon -   and save that result by name on the calculation


            in your case the LOD will sum the sales at the state level  and when you bring the calculation to the viz you need to aggregate the LOD - for fixed stat and sum it yields this


            Note the value for each category is not the same ?????  - check the data - yo will see that some states have null values at the state category level the affect the data




            • 3. Re: understanding LOD - By replicating results in excel
              Vikash Kumar

              My bad. I have attached the correct file along with the Superstore data set.

              I have another query regarding the values populated under the column ("Fixed State", which is the name of the LOD) when i click on the bar or the value for the sub-category 'Accessories' and select view data'

              I am unable to understand those values.

              • 4. Re: understanding LOD - By replicating results in excel
                Jim Dehner

                thanks for adding the file and BTW testing a checking the LOD forms is a good thing to do - I did it when I first started - refer back to my first post - the same concept is here in detail


                Understand that the LOD can only add data to the viz where there is a combination of the dimensions in the sheet itself where the value can bel placed - see below

                this is you fixed by state and all the states are present - see the total

                this is the vis when I select only accessories as a subcategory and still used the fixed total of sales at the state level

                for every accessories/state combination that is NOT empty (i.e. there is a record in the dat set) the lod places the sum of sales for the entire state - they are the same vaues as in the viz above

                BUT notice there was no accessories record for Wyoming - so it is not included in the viz below and the total is different than above


                Does that help explain what is going on


                Keep practicing to develop your own understanding



                If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

                • 5. Re: understanding LOD - By replicating results in excel
                  Vikash Kumar

                  Thanks a lot JIM for your time to explain my doubt in detail. It is a lot clearer now what is happening when we sync up the FIXED LOD calculation with the View level of detail.

                  • 6. Re: understanding LOD - By replicating results in excel
                    Jim Dehner

                    Keep working with them and also experiment with when to place the dimension in a context filter and when not - the results will be different when you filter


                    Glad to help out