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

    Complex trim involving sorting

    Roberto Alvarez

      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

      AmountRate
      100.3
      150.2
      5-0.4
      2.50.1
      20-0.5
      10-0.3
      100.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

      100.3
      150.2
      2.50.1
      100.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