3 Replies Latest reply on Sep 12, 2019 5:03 AM by Simon Runc

    Create a dynamic 'Ranking' using Window_sum or LOD

    Conor Duke

      Hi,

       

      I have a dataset where I would like to get Average, Median and Std Dev on a table when I apply a cut-off threshold based on a ranking .

       

      I sort my data by Sales revenue, and then establish a 'cut-off' when the 'Rank' >  'Expected Rank'.

      (Expected Rank is derived using Revenue and a static variable).

       

      I would then like the Average, median and Std. Dev of all the Values above and below the threshold.

       

      (see table below for examples)

       

      I am aware I can't use an Ranking LOD, but I was wondering if I could use an 'include' or Lookup function.

       

      If I was using Excel, I would simply create a pivot table from my data, and filter on the new derived field.

       

      See the example below,

       

      Thanks,

      Regards

      Conor

       

       

      Example Table

       

      Company NameThresholdSales RevenueCum Sales RevenueExpected RankActual rank
      aAbove100.0100.01.41.0
      bAbove90.0190.02.72.0
      cAbove80.0270.03.93.0
      dAbove70.0340.04.94.0
      eAbove60.0400.05.75.0
      fAbove50.0450.06.46.0
      gBelow40.0490.07.07.0
      hBelow30.0520.07.48.0
      iBelow20.0540.07.79.0
      jBelow10.0550.07.910.0

      Expected rank = (Cum Sale Rev / 70)

       

      Desired Results

       

       

      Desired Results in Dashboard

       

      Average70.0
      Median70.0
      Standard Deviation21.6
        • 1. Re: Create a dynamic 'Ranking' using Window_sum or LOD
          Simon Runc

          hi Conor,

           

          Just a quick question ...

           

          I'm not sure I understand the expected rank, as I don't get those values if we use cumulative sales revenue/0.8

          • 2. Re: Create a dynamic 'Ranking' using Window_sum or LOD
            Conor Duke

            Hi Simon,

            That was an error on my part, the expected rank is actually 'Cum Sales / 70'.

             

            I have updated it accordingly.

             

            The whole idea is to get the 'Expected Rank' to eventually become less then the actual rank... then classify the difference.

             

            Thanks for taking the time to look over my question

            Regards

            Conor

            • 3. Re: Create a dynamic 'Ranking' using Window_sum or LOD
              Simon Runc

              That makes more sense!

               

              So this is a bit complicated, as we need to use table calculations and also table calculation filters are applied last, meaning that although you filter the view (with your above/below) you are not filtering the underlying data. I've done Average, but you can follow the same method for the other stats...

               

              First I created all your calculated fields, and created this

              [Above/Below]

              [Expected Rank]>[Actual Rank]

               

              Then to get the average I did this....

              [Average]

              WINDOW_AVG(

              if [Above/Below] then sum([Sales Revenue]) end)

               

              So this get's the right answer, but we have it repeated for every row, as we need company in the vizLoD for the table calculations to compute as you want....

               

              So what we need to do, is alter the average calculation like so...

              [Average - single return]

              if first()=0 then

              WINDOW_AVG(

              if [Above/Below] then sum([Sales Revenue]) end)

              end

               

              we can then move the company to the detail shelf and answer will only be populated once, against the first one it comes to (else it's null and Tableau doesn't plot Nulls)

               

              Hope that makes some sense, and helps (btw all Table calculations are set to compute using Company)

              1 of 1 people found this helpful