
1. Re: Sum then Percentile aggregation  how to?
Simon Padget Jul 7, 2016 3:11 AM (in response to Lukas Mol)1 of 1 people found this helpfulHi 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

2. Re: Sum then Percentile aggregation  how to?
Lukas Mol Jul 7, 2016 4:45 AM (in response to Simon Padget)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)