5 Replies Latest reply on Jul 5, 2017 2:49 AM by Yuriy Fal

# Complex trim involving sorting

Hi all,

I have two measures, amount and rate, on a time series,  and I need to perform a complex trimming before calculating the weighted average. The trimming procedure is: for each day of the series first sort by rate (higher to lower) and then take out 25% of the total amount starting from the top and the bottom, so I end up with the  50% of the amounts that has less extreme rates. An example for a given day would be:

day 1

 Amount Rate 10 0.3 15 0.2 5 -0.4 2.5 0.1 20 -0.5 10 -0.3 10 0.1 15 -0.1 2.5 -0.3 10 -0.2

this is the data for one day (and there are many days in the same data). Each line is a record for a a given day. The trimming procedure would sort the data for each day from highest to smallest rate, and then remove 25% of the total amount from each side. Since the amount here adds up to 100, the 2 first and lasts record of the following table (in bold letters) would be trimmed out for day one:

day 1

Amount      Rate

 10 0.3 15 0.2 2.5 0.1 10 0.1 15 -0.1 10 -0.2 10 -0.3 2.5 -0.3 5 -0.4 20 -0.5

Then, with the remaining records (the non-bold ones), I would calculate the weighted average and plot it in a time series line graph. I managed to do the trimming through table calculations for the disaggregated data but then I cannot aggregate them since removing the record number from the detail causes the crosstab to go blank. LOD expressions seem not to be an option, as I need a running_sum to do the trim. Any suggestions? Is this doable with tableau?

Thanks  lot

• ###### 1. Re: Complex trim involving sorting

Hi Robelto

Kind of complicated, but I tried to explain step by step.

25%%75% percentile calc.

Not sure weighted calc is correct or not,, you can modify based on your needs.

Set table calculation appropriately.

Put Index filter

Thanks,

Shin

• ###### 2. Re: Complex trim involving sorting

Thanks! That was very useful. Now I managed although since the data volume is very big, the computing time is very high, a few minutes.. if you have any other sugggestion to improve it , I'd be very happy to hear it. Thanks again!

• ###### 3. Re: Complex trim involving sorting

Hi Robeto

Unfortunately, I don't know alternative method at this point.

Thanks,

Shin

• ###### 4. Re: Complex trim involving sorting

Hi guys,

There is one other way of doing the calcs, namely,

by using a "simpler" PERCENTILE() aggregation --

if the underlying DBMS can do it (not every one can).

Luckily, Tableau Data Engine can do it :-),

so please find the attached workbook

(I took the one by Shin).

ID-ing every row (each time series event) is important

since the NTile (Filter) calc is based upon FIXED LODs

at the granularity of the datasource (every single row).

A caveat: just because of the FIXED,

the whole thing would be slow either.

Yours,

Yuri