3 Replies Latest reply on Jul 25, 2018 8:58 AM by Bryce Larsen

    LOD Calculation Issues

    Helen Cuffe

      First, I cannot attach data or a workbook at this time since the data is sensitive (also complicated to make a fake dataset). I know I am breaking the rules and it is hard to fix my problem without the attachments, but I am going to try anyway.

       

      I am having trouble with a LOD calculation for a denominator. I have a spreadsheet with rows, the COUNT of which is the denominator. A certain subset of the rows is the numerator, and I want to calculate a simple rate (num/denom)*100.

       

      I have several filters/variables that need to affect the data. Some (location, date) need to adjust the denominator and numerator. Others (type, level) only need to affect the numerator while the denominator remains consistent.

       

      Here are a few Calculations I have tried for the denominator:

       

      {fixed [Date], [Location]: SUM({include [Date], [Location]:COUNT([Row ID)})}

       

      {fixed [Date]: SUM({include [Date], [Location]:COUNT([Row ID)})}

       

      {fixed [Date]: SUM({exclude[Type], [Level]:COUNT([Row ID)})}

       

      With various other iterations. All of them return incorrect numbers for the denominator. Moreover, when a numerator value is 0 for a particular time (ex. March 2018), tableau does not seem to include the March denominator in the overall 2018 aggregation, even though the numerator is non-0 for other months, and aggregates just fine.

       

      ANy ideas? I would do this in R in just a few minutes, but due to multiple reasons I cannot link R to this workbook.

       

      Thanks!

       

      HC

        • 1. Re: LOD Calculation Issues
          Bryce Larsen

          Hi Helen,

          Please confirm: numerator is not a LOD express, just a calculation, correct?

           

          You should be able to just do {COUNT([Row ID])} as you've done above, but then right-click on Location and Date in the filters and select 'Add to Context'. This should change the filter to a gray color, but that might just be it. It essentially partitions the data in this sheet. Please let me know if this works! Hard to say without being able to test.

           

          Best,

          Bryce

          • 2. Re: LOD Calculation Issues
            Helen Cuffe

            So numerator is just a simple COUNT, and it works...

             

            When I did that the COUNT of the denominator with the added context and displayed by the numerator type, it only counts where the numerator is valid.

             

            Ex. I have 1000 rows (denominator), 100 of those 1000 are events (numerator). So rate should be 100/1000... of the total numerator, 60 are type 1 and 40 are type 2. Therefore the rate for type 1 is 60/1000 and the rate for type 2 is 40/1000. What tableau gives me for the denominator (after adding the contexts for date and location) is 100 for the total, 60 for type 1, and 40 for type 2 etc...

             

            I will try to find some time today to fake this data and post it. I know that would be MUCH easier!

             

            THANKYOU!!!!!

            • 3. Re: LOD Calculation Issues
              Bryce Larsen

              That would be appreciated! Hmm. Wonder where it's breaking down. I just made some fake data:

              • 100 rows per two locations and two dates
              • 6 rows for Type 1 and 4 rows for Type 2 to keep ratio
              • identical numerator/denominator columns (values just 1-100 for each location/date group)

               

              I was able to create a new denominator using the COUNT LOD with items added to context, and numerator appropriately affected by the filters.

               

              Hopefully someone else can help out or you can find time to fake some data! Best of luck.