6 Replies Latest reply on Jan 21, 2014 4:00 PM by Richard Leeke

    Computing SUM over 10-90 Percentile broken down by Client

    laurent.carlier

      Hi,

      I have an example attached with a serie of sales for 2 clients X and Y.

      I'd like to see a report where for each client, I have the sum of the Sales computed over their respective 10% to 90% percentile.

      For example, there are 10 entries for client X.

      I'd like to see the SUM of Sales where we have removed the bottom 1 and top 1 entries => should give  2,150

      For client Y, there are 20 entries:

      I'd like to see the SUM of Sales where we have removed the bottom 2 and top 2 entries => should give  6,600.

      Thanks

      Laurent

        • 1. Re: Computing SUM over 10-90 Percentile broken down by Client
          Prashant Sharma

          Hi Laurent,

          In your attached workbook X & Y client is having only two values. Sales is not discrete here, means for X how can we find the top & bottom as there is only a single dimension & single measure. BTW, Use the below formula: -

          if attr([Department]) == 'Furniture' then (first() == 0 or last() == 0)

          else if attr([Department]) == 'Office Supplies'  then

          (first() == 0 or last() == 0 or first() == -1 or last() == 1) end end

          Here replace [Department] with your client field. You also need to put the indexes for each sales entries so that it will show the discrete values. Put this field in filter selection & choose 'False'. Hope this may help you!

           

          Warm Regards,

          Prashant Sharma - India | LinkedIn

          • 2. Re: Computing SUM over 10-90 Percentile broken down by Client
            Richard Leeke

            I've added a couple of sheets to your workbook - one giving just the answer you want and one showing some more fields to show how it works.

             

            It's a bit crude and I haven't thought through how it will work if your clients don't happen to have a multiple of 10 sales records - but I think the "correct" answer is a bit ill-defined in that case anyway. If there are large numbers of sales per customer the impact of that is probably less important.

             

            It basically works by counting how many rows there are with each value and omitting the first 10% and last 10%. It would be slightly easier if your data had a unique ID for each row, rather than having to generate an index on the discrete values and multiply by the number of records for each.

             

            Have a look at the sheet [Workings] and see if it makes sense. Note in particular that the table calculations on the Measure Values shelf ([Index], [Rank] and [Sale_10_to_90]) have their "Compute Using" set to [Sale] - which means that there is a partition for each customer and the calculations look at all [Sale] values in each partition sorted by ascending [Sale] value.

             

            The [Answer] sheet just shows the [Sale_10_to_90] field and filters down to a single row by just showing the row with [Index] = 1. Note that that filter is applied after all of the calculations have taken place because the filed formula is the Index() table calculation function and filters on table calculations have to be applied after the table calculation has been evaluated.

            • 3. Re: Computing SUM over 10-90 Percentile broken down by Client
              laurent.carlier

              Thanks a lot Richard, it worked nicely.

              • 4. Re: Computing SUM over 10-90 Percentile broken down by Client
                laurent.carlier

                actually I have an extra question:

                I have added to your report an extra computation which is the AVG as opposed to the SUM.

                I can disaply the two measures, but cannot switch to the type "text tables" in the upper right "Show Me" section.

                This is a bit inconvinient.

                Is there a workaround ?

                Thanks

                 

                • 5. Re: Re: Computing SUM over 10-90 Percentile broken down by Client
                  Richard Leeke

                  You need to use "Measure Names" and "Measure Values" - special Tableau fields which allow you to do what you want. Have a read on them in the online help.

                   

                  Updated workbook attached.

                  • 6. Re: Re: Re: Computing SUM over 10-90 Percentile broken down by Client
                    Richard Leeke

                    Actually, after posting that last response I noticed that my answers for the average weren't the same as yours. Thinking about it I realised that we were both wrong.

                     

                    Your answer was giving the sum of all of the distinct values in the 10% to 90% range. Some of the values had 2 or 3 or 5 instances, so by taking the WINDOW_SUM() of the AVG() values you were effectively only counting each distinct value once (the AVG() just applied at the level of the distinct values).

                     

                    My first answer was also wrong, because I took the WINDOW_AVG() of the distinct values - which means that I didn't take account of the number of instances of each value. For example, if the numbers had been 1, 1, 1, 2, that would have given a value of 1.5 (the average of 1 and 2) rather than 1.25 = (1+1+1+2) / 4.

                     

                    The latest attachment has a correct version, calculated as the overall total divided by the overall number of records in the range.