3 Replies Latest reply on Oct 2, 2016 1:19 AM by Wayne Ooi

    LOD for Top Line Result

    Wayne Ooi

      Hi,

       

      I am trying to use LOD to achieve below Score Results by:

       

      • Customer
      • Sales Person
      • Sales Manager
      • Top Line

       

      I could only achieve part of the "by Customer" result but having problems for the rest.

       

      Below are the details of the datasource and Tableau Workbook

       

      Data:

      Screenshot 2016-10-02 12.43.09.png

       

      Tableau Workbook:

      Screenshot 2016-10-02 12.49.04.png

      by Cust

      I would like to show also the Grand Total Score: 7.03. I created a calculated field: Total Score = WINDOW_SUM([Score]) but I can only show it as Tool Tip.

      Screenshot 2016-10-02 12.49.45.png

      by Sales Person

      - Sum of Score of individual customers that belongs to that Sales Person

      - The desired result are:

      Sales Person 1: 0.89 + 0.39 + 0 = 1.28

      Sales Person 2: 2.22 + 1 + 0.28 = 3.5

      Sales Person 3: 0 + 0.33 + 0.07 = 0.4

      Sales Person 4: 0.8 + 0.56 + 0.5 = 1.86

      Screenshot 2016-10-02 12.50.40.png

      by Sales Manager

      - Sum of Score of individual customers that belongs to that Sales Manager or Sum of Sales Person's score that belongs to the Sales Manager

      - The desired result are:

      Sales Manager A: 1.28 + 3.5 = 4.78

      Sales Manager B: 0.4 + 1.86 = 2.26

      Screenshot 2016-10-02 12.51.56.png

      Topline

      - The desired result: 7.03

      - i.e. sum of all individual score of each customers

      Screenshot 2016-10-02 13.19.44.png

       

      Below are the formula and criteria to calculate score:

      1. Score Volume

      - Score Volume is derived via Annual Volume, however, only if the CRM Score is more than 0 else it won't be considered

      Screenshot 2016-10-02 13.03.05.png

       

      2. Score Index

      - Score Volume / Total Score Volume

      - Total Score Volume is 9000

      - Score Index for customer C01 = 1000 / 9000

       

      - Total Score Volume is dynamic, depending on what user's filter; for e.g. user can filter to Sales Person 1, 2 and 3 then the Total Volume in this case will be only 5900 instead of 9000

      - Score Index for customer C01 = 1000 / 5900

      Screenshot 2016-10-02 13.04.01.png

       

      3. Score

      - Score Index * CRM Score

      Screenshot 2016-10-02 13.04.45.png

       

      I have tried LOD with INCLUDE and EXCLUDE as well but  still not able to achieve what I need. Really appreciate if someone could help.

       

      Thanks,

      Wayne.

        • 1. Re: LOD for Top Line Result
          Shinichiro Murakami

          You can simplify score calc.

          [SM Score2]

          [CRM_Score] * [Score Volume]/{fixed:sum([Score Volume])}

           

          1 of 1 people found this helpful
          • 2. Re: LOD for Top Line Result
            Wayne Ooi

            Hi Shinichiro,

             

            Thank you for your reply.

            I tried on the calculated field you suggested and they work fine if there is no filter applied to the dataset.

             

            I tried to change the suggested calculated filed so that they response to the filter but not successful.

            Would you be able to tell me please how I can change the calculated field so that it can response to the filter. For e.g. user can filter out certain Customers, Sales Persons or Sales manager.

             

            No Filter: Results are correct

            Screenshot 2016-10-02 20.26.17.png

             

            Filter out Customer C01: Results are not correct

            - Once data is filtered out, the score volume of that row should not be taken into consideration

            - Example in this case, the Score Index for C02 = 500 / 8000

            - Score for C02 = (500 / 8000) * 7 (please see last Excel Screenshot)

            - Total Score Index should always add up to 1

             

            Correct Results are:

            Top Line should be 6.91

             

            by Sales Manager:

            A: 4.38

            B: 2.54

             

            by Sales Person:

            1: 0.44

            2: 3.94

            3: 0.45

            4:  2.09

             

            by Customer: as per Excel screenshot

            Screenshot 2016-10-02 20.28.30.png

             

            Excel Screenshot:

            Screenshot 2016-10-02 20.41.34.png

             

            Thanks,

            Wayne.

            • 3. Re: LOD for Top Line Result
              Wayne Ooi

              Hi Shinichiro,

               

              I think I might have found the solution, I amended the suggested calculated fields and below are what I am doing:

               

              Step 1: Create Calculated Field: Score Volume

              {INCLUDE  [Customer] : SUM(IIF([CRM Score]>0,[Annual Volume],0))}

               

              Step 2: Create Calculated Field: Score Index

              [Score Volume] / {EXCLUDE  [Sales Manager], [Sales Person], [Customer]  :SUM([Score Volume])}

               

              Step 3: Create Calculated Filed: Score

              [CRM Score] * [Score Index]

               

              Thank you again for your help.

               

              Thanks,

              Wayne.