7 Replies Latest reply on Aug 14, 2019 5:54 PM by Jim Dehner

    Grand Total for LOD Calc Incorrect When Missing Data

    Phillip Wallace

      //I have updated this to include a sample workbook, as recommended by Naveen B and Deepak Rai

       

      I have a crosstab in which I am calculating row grand totals for three measures:

       

      //This may be unnecessary for this question, but I'm adding it in case it helps.

      • CALC 1: simply uses COUNTD
      • CALC 2: [CALC 1] / SUM({FIXED : [BLAH]})
      • CALC 3: SUM({FIXED [DIM1],[DIM2],[DIM3]:[CALC 1]}) / SUM({FIXED [DIM2],[DIM3]:[CALC 1]}), and the

       

      I've noticed that the grand total for CALC 3 is correct as long as there is something in each quarter (I have Quarter on the Columns shelf). But when at least one quarter is missing data, the grand total will not calculate correctly.

       

      Any help on this?

       

      Message was edited by: Phillip Wallace

        • 1. Re: Grand Total for LOD Calc Incorrect When Missing Data
          Naveen B

          Hi Philip,

           

          could you please attach a sample workbook in twbx format to help you further

           

          BR,

          NB

          • 2. Re: Grand Total for LOD Calc Incorrect When Missing Data
            Deepak Rai

            Can you create a  workbook with Superstore data and attach?

            • 3. Re: Grand Total for LOD Calc Incorrect When Missing Data
              Deepak Rai

              LoD was not Well , Since Corrected, Check below Please:

              Use This Calc for LOD:

               

              {FIXED DATEPART('year', [Order Date]), [Customer Name (group)]:COUNTD([Order ID])}/({ FIXED:COUNTD([Order ID])})

               

              Thanks

              Deepak

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

              • 4. Re: Grand Total for LOD Calc Incorrect When Missing Data
                Jim Dehner

                Hi

                 

                this calculation will replace the totals in the Grand Column where the size() is always = 1

                 

                 

                 

                 

                 

                your book is attached

                 

                Jim

                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.

                1 of 1 people found this helpful
                • 5. Re: Grand Total for LOD Calc Incorrect When Missing Data
                  Phillip Wallace

                  Thanks for the quick reply, Deepak. While this solution gets me the correct grand total, it creates a different problem by changing the calculation so that each cell is divided by the Order Count grand total (2,464) instead of the order count for that year. For example, LOD Test should be 32.8% (207 / 632), but it now shows 8.4% (207 / 2,464). Jim's approach solved the issue.

                   

                  Thanks again for being so quick to help - one of the best features of Tableau is how we all help each other get better!

                  • 6. Re: Grand Total for LOD Calc Incorrect When Missing Data
                    Phillip Wallace

                    Thanks, Jim! That did the trick.

                     

                    For you folks following along at home (or work), my initial calculated field had the following:

                     

                         [Order Count]/SUM({ FIXED YEAR([Order Date]):[Order Count]})

                         //Order Count = COUNTD([Order ID])

                     

                    That evaluated perfectly in every scenario except for the grand total for a row in which there was no data in or or more years. The reason is that my denominator, which should have been the sum of the # of orders in each year, excluding the # of orders for 2017.

                     

                    The reason for this is since there were no data in the partition for that year (remember: "no data" is different than "null" - i.e., in this case, there was no row of data in that partition with an order date in 2017), Tableau could not calculate a distinct count for that year. The expression thus calculated  207 / (492 + 541 + 632) = 207 / 1,665 = .363 => 36.3%.

                     

                    Jim's solution did a few things:

                     

                    1. Provided different expression for the grand total (if size() = 1)...
                      1. Numerator is the distinct count for the entire row (605 for the first row)
                      2. Denominator is the distinct count for the entire table (2,464) - this addressed the issue caused by there being no data in 2017 in that one row.
                    2. Modified expression for the cells
                      1. Numerator is the # of orders at the level of row (the hastily "named" Customer Name (group)) and year.
                      2. Denominator is the # of orders at the level of year.

                     

                    Jim Dehner, I do have a question: I understand why size()=1 is necessary (the grand total partition size is 1), but is ZN() necessary? It seems to not change things whether I include or exclude them. I will probably incorporate something that uses ZN() in the real dashboard to provide zeroes instead of blanks.

                    • 7. Re: Grand Total for LOD Calc Incorrect When Missing Data
                      Jim Dehner

                      as to the ZN()  - I was not certain it was necessary but when you write a fixed lod you are creating an additional layer (virtual) in the data set - in your case the layer is based on a count at the year level - you have some yeaars without data so the the value for the lod is a null  - in the denominator we sum across all years so I dropped the zn() in just to make certain we didn't return a Null   - BTW it does not

                       

                      Glad to help out

                      Jim

                      1 of 1 people found this helpful