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

# Sum then Percentile aggregation - how to?

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?

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?

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: