2 Replies Latest reply on Jul 7, 2016 4:45 AM by Lukas Mol

    Sum then Percentile aggregation - how to?

    Lukas Mol

      I'm fairly new to Tableau, googled my problem but didn't get to a solution, so I'm reaching out here.

       

      How do I approach the following analysis? I'm simplifying the steps below to a single end-value.

       

      Starting point: 10 days of delivery volumes for 4 customers (i.e. 40 values). The four customers are comined in to same delivery run on each of the 10 days.

      End goal: percentile (e.g. 80) volume of the 10 delivery runs.

       

      So I need to sum the 4 customers' volumes across each of the 10 days to get the 10 days total volumes.

      Then of those 10 days I want to get the 80% percentile.

       

      e.g.:

      day 1: 10+12+9+10 = 41

      day 2: 9+15+12+6 = 42

      day 3: 12+8+8+6 = 34

      etc. for 10 days

       

      then percentile(80%) of (41,42,34.... etc for 10 values in total)

       

      Obviously all of this actually needs to happen over a much bigger data set. I'm just not finding a way to sum by date and then percentile accross dates.

        • 1. Re: Sum then Percentile aggregation - how to?
          Simon Padget

          Hi Lukas,

           

          I think you can do this with a couple of Table Calculations...OR level of detail

           

          With table calcs:

          Basically, you need a WINDOW_SUM to sum all customers across a day. And then you can use a WINDOW_PERCENTILE to get the 80th percentile across the whole set.

           

          So your window sum would be:

          WINDOW_SUM( SUM([Volume]))

          this needs to be set up as an "advanced table calculation" to partition by Day, and address Customer.

           

          Your percentile will be:

          WINDOW_PERCENTILE([c_window sum], 0.8)

          and this needs to be set up to address both Day and Customer.

           

          This should work OK for a much larger dataset, but remember you will need these pills in view so is limiting in certain ways.

           

          With Level of Detail:

          You need to sum the volume for each day using a FIXED calculation. Then you can do a simpler WINDOW_PERCENTILE on that.

           

          So the FIXED calc is:

          {FIXED [Day] : SUM([Vol])}

          note here we are fixing the SUM of volume for each day

           

          Your percentile will be:

          WINDOW_PERCENTILE( SUM([c_fixed day volume]), 0.8)

           

          which again should work fine on a much larger dataset. This also affords you a little more play with how you present it as you do not need "customer" in your view

           

          I will add a workbook shortly to explain more

           

          Simon

          1 of 1 people found this helpful
          • 2. Re: Sum then Percentile aggregation - how to?
            Lukas Mol

            Hi Simon,

             

            Thanks for your pointers... I managed to get it working using the LOD approach.

             

            I've created a calculated measure

            VolPerBakRitWeek = {FIXED [Bakkerij],[Rit],[Dag]:SUM([LD Gecshaald en Afgerond])}

            which sums the client volumes (=LD Gecshaald en Afgerond) by Source (=Bakkerij) and Vehice run (=Rit) and Date (=Dag)

             

            The table values are:

            PERCENTILE([VolPerBakRitWeek],[Dekkingsgraad])

            where [Dekkingsgraad] is a parameter between 0 and 1

             

            Output table then results as below. Which is the intended behaviour (i.e. verified with the underlying data)

            Capture.PNG