3 Replies Latest reply on Sep 1, 2018 10:45 AM by Shinichiro Murakami

    Applying rank in other calculations

    Heidi Kiser

      Hello,

       

      I've been using Tableau 18.1 for about six months and rapidly getting in over my head.

       

      In the attached example, I have created an algorthm which is the sum of scores from different criteria.

       

      For instance, I created the tab RankCust (or ranking of Customers by their sum of [Cust-AvgMoFlights]) and want to use this rank to apply a score to those sites in the tab GlobalPOAPlan, [Score-Cust]. In this case, my criteria are Rank 1-25 = 5, 26-50 = 4, and so on.

       

      Another criteria is RankPrior (or ranking of the [Priority-AvgMoFlts]) and want to use the same criteria as above in Score-Priority, Rank 1-25 = 5, 26-50 = 4, and so on.

       

      In both of these, when I drag the [RankIATA-CustFlts] and [RankIATA-PriFlts] to the Measures card, they change value instead of retaining the value achieved in the relevant tabs. How do I prevent this?

       

      Next I have created a 10 ten of [Arr Airport] in Brazil and Russia in tab RankBrRu, and I want to make only these 20 [Arr airport] each receive a score of 5 in the [Score-BrRu]. Again, when I insert [Score-BrRu] into my GlobalPOAPlan, it changes value.

       

      Finally, in [Score-LargeGaps], I have tried to apply a value to specific [RegCountry...], yet these values do not appear correct in the tab GlobalPOAPlan.

       

      Eventually, I want to be able to sum each of the the four scores in [Score-Sum], but when I tried to create this Calc Field, it game me an error.

       

      Finally, I will rank the [Arr Airport] by [Score-Sum] and designated some "In Plan" and others "Not".

       

      Can you assist me in achieving this?

       

      Much appreciated!!

       

      Heidi

        • 1. Re: Applying rank in other calculations
          Peter Fakan

          Hi Heidi,

           

          I got as far as In both of these, when I drag the [RankIATA-CustFlts] and [RankIATA-PriFlts] to the Measures card, they change value instead of retaining the value achieved in the relevant tabs. How do I prevent this?

           

          My approach was to take all of the unnecessary measure values out of the card, and rebuild following your directions. When I drag the [RankIATA-CustFlts] and [RankIATA-PriFlts] measures into the measure values area they aren't changing the values.

           

          I then came unstuck on the next part - Next I have created a 10 ten of [Arr Airport] in Brazil and Russia in tab RankBrRu, and I want to make only these 20 [Arr airport] each receive a score of 5 in the [Score-BrRu]. Again, when I insert [Score-BrRu] into my GlobalPOAPlan, it changes value.

           

          I'm not sure what creating a 10 ten means - do you mean just filter on the top 10 ? Then what do you mean in the next part - I want to make only these 20 [Arr airport] each receive a score of 5

           

          For example if I've filtered that to top 10, then why do I need to to recalculate 20 ?

           

           

          HTH

           

          Peter

          • 2. Re: Applying rank in other calculations
            Heidi Kiser

            Peter,

             

            You did not attach a workbook to the response, so I cannot understand your answer.

             

            In Question 1 and 2:

            I got as far as In both of these, when I drag the [RankIATA-CustFlts] and [RankIATA-PriFlts] to the Measures card, they change value instead of retaining the value achieved in the relevant tabs. How do I prevent this?

             

            My approach was to take all of the unnecessary measure values out of the card, and rebuild following your directions. When I drag the [RankIATA-CustFlts] and [RankIATA-PriFlts] measures into the measure values area they aren't changing the values.

             

            I find that the correct values do not display in the sheet GlobalPOAPlan. A quick test of Airport CRL, Charleroi Belgium, shows a score of 5 for both Customer and Priority AvgMoFlts, but when I go to those worksheets, RankCust and RankPrior, CRL does not appear in the top 25 of either list when [Arr Airport] is sorted based on the AGG((Cust-AvgMoFlts) and AGG(Priority-AvgMoFlts). The Airports in GlobalPOAPlan that have a score of 5 should be those that are rank 1-25 in the corresponding worksheet.

             

            My new Question 3 is the calculated field [Score-Sum] will not sum the values requested because some are aggregates and some are not. How do I get the result then?

             

            Please attach a workbook so I can better understand and see how you do these things. Thank you,

             

            Heidi

            • 3. Re: Applying rank in other calculations
              Shinichiro Murakami

              HI Heidi,

               

              I looked thru your file.

              Unfortunately, this is too heavy to be handled in forum.

              Whole the calculation need to be re-build from the scratch.

              And on top of that, high level training for "rank" or "table calculation" is needed.

              In other words, someone in forum need to take over whole the project from you.

              That's not fair request in the forum for this level of complexity.

               

              Best Regards,

              Shin

               

              .