5 Replies Latest reply on Jul 22, 2016 2:57 PM by John Coven

    Defining numerator and denominator of a ratio separately with filters and/or parameters

    John Coven

      Hello all,

       

      I have an interesting challenge before me. I have data on attempted package deliveries within several cities in a specific timeframe. I want to present the percentage of deliveries (by city) which "fail" in map form. Problematically, deliveries can be thought of as having failed for any of a variety of reasons (weather, the recipient not being home, delivery truck problems, etc.), not all of which will be of interest at a given time or for a given problem, so I'd like the user to be able to define "failure" by selecting one or more of several possible reasons.

       

      Also problematically, I want the population of deliveries to be separately customizable (e.g., overnight vs. standard deliveries, deliveries from particular customers, etc.), since we will not always be interested in the same population of deliveries.

       

      In other words, I need Tableau to be able to present a ratio where both the numerator and denominator are separately defined -- so, e.g., I cannot just use filters to define the numerator without inappropriately affecting the denominator. (After all changing the definition of failure does not objectively change the number of attempted deliveries!)

      Can anyone advise on how to proceed here?

       

      Thanks so much,

       

       

      - JC

        • 1. Re: Defining numerator and denominator of a ratio separately with filters and/or parameters
          Stoyko Kostov

          Hello John,

           

          This article may be discussing a similar problem.

           

          https://www.interworks.com/blog/jeverett/2014/05/12/3-ways-preserve-percent-total-within-filtered-dimensions

           

          It looks like the fastest solution could be to duplicate your data source, and define the numerator (and its filters) on one copy, and the denominator (and its filters) on the other copy.

           

          Hope this helps. Don't hesitate to ask if you have other questions.

          • 2. Re: Defining numerator and denominator of a ratio separately with filters and/or parameters
            Sara Sparks

            Hi John!

             

            Welcome to Tableau Community!

             

            We should be able to accomplish this using Level of Detail calculations, if you're using Tableau Desktop 9.0 or above.

             

            We can use a parameter to let users choose which condition they would like to consider as "failure", and then count the deliveries for the numerator conditional upon those selection, and count the deliveries for the denominator separately, filtering the view by the second condition. I have attached a sample workbook with a similar solution.

             

            The workbook displays the percentage of deliveries that can be considered "Late" for each state, dependent on the Ship Mode selected. So we are evaluating:

             

            Number of shipments that are a specific ship status AND belong to a specific Ship Mode / Total number of Shipments for a Specific Ship Mode

             

            This looks like this in a calculation:

             

            SUM({FIXED [Order ID]: IF MAX([Ship Status]) = [Choose Status] THEN 1 END})

            /COUNTD([Order ID])

             

            I have attached the sample workbook, with a similar solution.

             

            If this doesn't resolve the issue, I would recommend posting a sample workbook, with data that mirrors your own, to give Community members a little more detail on what you are trying to accomplish!

             

            Thanks!

            • 3. Re: Defining numerator and denominator of a ratio separately with filters and/or parameters
              John Coven

              Hi Stoyko,

               

              Thanks for this suggestion. Unfortunately, duplicating the data source isn't a very feasible option, in part because the data source is exceedingly large (nearly 25 million records) and in part because all the filters which apply to the numerator must also apply to the denominator.

              • 4. Re: Defining numerator and denominator of a ratio separately with filters and/or parameters
                Stoyko Kostov

                Hi John,

                 

                The size of the data source shouldn't matter, unless you need to package your workbook. If it only has live connections, you shouldn't be worried about duplicating it. Once it is filtered accordingly, the computations will only be done on the filtered part. In case of live connections, duplicating doesn't mean the data source would physically be stored twice - in memory or on disk.

                 

                Also, can you help me understand your question better? From your original post I thought I understood you needed separate filters on the numerator and denominator, and that's the reason I suggested duplicating the data source. Even if some, or even most, filters on these 2 fields are the same, as long as they differ even a little, you would need some maneuvering to deal with the situation.

                 

                I recommend you still give the duplication option a try, and toss it out only if it visibly degrades performance. In that case, try the second suggestion in the link above - In/Out sets. Consider Sara's suggestion as well.

                 

                Would it be feasible for you to attach a sample packaged workbook, so we understand better your problem? Take a small sample of the data to include.

                • 5. Re: Defining numerator and denominator of a ratio separately with filters and/or parameters
                  John Coven

                  Hi Stoyko,

                   

                  OK, thanks for the heads up re: duplication. I am running from an extract at the moment, because the live connection is slowing down my progress here considerably.

                   

                  Yes, I may not have been clear re: the problem. Basically, I have k filters that the user can set to determine the numerator of my ratio. Of those k filters (where k is currently 7 but could grow or shrink in the next week), I need k-2 of them to affect the denominator. (The k-2 filters determine the population of interest, the remaining 2 filters determine which events within that population are of interest).

                   

                  I am trying out the duplication option now, having temporarily given up on getting LOD expressions to work (in one case, just adding a new filter to the FIXED expression causes my denominator to drop, even before filtering anything). I am unsure, though, how to make it work: would it then be necessary to link the two data sources (numerator and denominator) using whichever variables on which I want both to filter? Weirdly, this results in my denominator shrinking just by linking the two, even before I actually filter them, the same problem as with LOD expressions.

                   

                  I will work on getting a small sample of data into Tableau form that can I post here.