3 Replies Latest reply on Aug 21, 2018 6:51 PM by Okechukwu Ossai

    Using FIXED LOD but total counts in charts don't match

    conacher

      Hello,

       

      I'm using a FIXED LOD calculation to calculate the total records for a particular month so it can be displayed as well as used as a divisor in another calculation.  The only columns affected by the incorrect data are the last columns (July 2018) and I believe it's because we started coding some claims as "New" There is no cross-reference between Future Medical (green) and "New" so I can understand that count being off (although I don't know how to fix it) but the second chart is off in July 2018 as well in both the indemnity (purple) and medical restricted (pink) categories. 

       

      I'm not opposed to changing how I calculate the total but I don't know how else to achieve the results I need.

       

      I've been wracking my brain trying to figure out a solution.  Any and all help would be appreciated.  Workbook is 2018.

       

      Thank you.

       

      ~S

       

      Tableau FIXED LOD but total counts in charts dont match 1.png      Tableau FIXED LOD but total counts in charts dont match 2.png

        • 1. Re: Using FIXED LOD but total counts in charts don't match
          daniel.stokes

          I don't have a good explanation for why this behavior is occurring, but as a workaround/solution I have found that specifically excluding the Type of Claim from the LOD provides the expected numbers:

           

               { EXCLUDE [Type Of Claim]: SUM([Total Claims for the Month]) }

           

          Going to keep playing with the workbook and see if I can find a better answer!

           

          Cheers,

          2 of 2 people found this helpful
          • 2. Re: Using FIXED LOD but total counts in charts don't match
            Michel Caissie

            The  Exclude Type of Claim  is the way to go.

             

            Since  the [Type Of Claim] = 'Future Medical' doesn't have any data with the status 'New' , the value  22 is not Summed for the corresponding bar.

            So if you add more granularity in the view than you have in your calculation, you will have to exclude those extra dimensions if you want to get the same numbers for every mark.

             

             

            Michel

            2 of 2 people found this helpful
            • 3. Re: Using FIXED LOD but total counts in charts don't match
              Okechukwu Ossai

              Hi Conacher,

               

              There are few options to get the correct result.

               

              Option 1: Use the Exclude LOD suggested by Daniel. [Total Claims for the Month]

              {EXCLUDE [Type Of Claim]: SUM({FIXED [Open Claim Month], [Status], [Table Name] : SUM([Number of Records])})}

              If you use this option, then [Examiner] filter should be added to context for the numbers to be correct. Verify how the new Examiner context filter is affecting other worksheets.

               

               

              Option 2: Modify the Total claims formula. [Total Claims for the Month_new]

              {FIXED [Open Claim Month] : SUM([Number of Records])}

              If you use this option, then all filters except open claim month should be added to context. Also, verify how the context filters will affect other views before making the filters applicable to multiple worksheets.

               

               

              Hope this helps.

              Ossai

              2 of 2 people found this helpful