4 Replies Latest reply on Jul 13, 2018 11:10 AM by Deepak Rai

    Scoring by vendor based on attributes

    Cristian Garofalo

      Dears,

      Im new in tableau and I have a basic question I could not sort it out.

       

      I Have connected a record set that contains vendor account payables, each row contains vendor#  which is the unique id of the vendor. Additionally, there other attributes about the amount paid to the vendor,  we want to assign a ranking  to each vendor based on a criteria defined by the attributes of the transaction.

       

      For Example

      1 point :  Vendor is in Argentina

      2 points : Vendor performed service X and Service Y

      2 Point :   Vendor provided services for 2 different legal entities

       

      As final output I expect to have a list with the vendors with its scoring, Therefore in the end, each vendor can at most have 5 points. 

       

      I.e

      Vendor Ranking

      XXX       4

      YYY       3

      ZZZ        1

       

      I was thinking to perform this using LOD calculation in the table  but I could not manage to get the results I expected - Simply because I think Im not using this on the right manner.

       

      { FIXED [Vendor Nr1]:

      SUM (IF ([Attribute 1]) = 'XXXXX'  AND ([Attribute 2]) = 'YYYYY'

                 THEN 1

                IF ([Attribute 2]) = 'XXXXX'  AND ([Attribute 3]) = 'YYYYY'

                 Then 2

           END)

      }

       

      CAn you please advice what would be the right way to implement this logic in Tableau?

       

      THanks a lot for your help,

      Cristian

        • 1. Re: Scoring by vendor based on attributes
          Abhinav Garg

          Good Morning Cristian!

           

          Very interesting question. My personal first choice of attempting this would be either in SAS or Python. I refrain from doing complicated calculations in Tableau unless the sole purpose of the calculation is making a visual.

           

          Don't get me wrong! You can still do this in Tableau. This is what I would do:

           

          1) I would make 3 calculated fields:

               a) Vendor is in Argentina

                              If (Location = 'Argentina')

                                        then 1

                              else 0

                              end

                b)  Vendor performed service X and Service Y

                              if(service = X or service = Y)

                                             then 2

                              else 0

                              end

                c) Vendor provided services for 2 different legal entities

                              if (entity1 = Legal and entit2 = legal and .... )

                                             then 2

                              else 0

                              end

                         Not exactly sure how your data looks like, so can't be 100% sure how you want this one to be written

           

          2) then in final step I would add the 3 calculated field.

                              a+b+c

                    Let's call it Score.

          3) apply the check to see if the sum is greater than 5, then make it 5 or whatever you want.

                         If Score>5

                              then 5

                           else score

                         end

           

          Looking forward to seeing if this logic works for you!

          Happy Friday!

          • 2. Re: Scoring by vendor based on attributes
            Cristian Garofalo

            Hi Abhinav!

            Thanks for replying ! ...  Can you please advice in which context you would great the calculated fields? Include? Fixed?

             

            Please remember that the in our example the maximum score can be up to 5, I assume that if I create the calculated field as suggested it will calculate the score per each single row, in a way if you have 10 entries for one vendor located in Argentina (Score 1) and the other criterias are not met , your scoring will be 10.  However, the expected result  is that the score of that vendor is 1 .

             

            Make sense my issue?

            • 3. Re: Scoring by vendor based on attributes
              Abhinav Garg

              I don't see any reason why you would want to use any LOD expression. Or maybe I am thinking in the wrong direction.

               

              Didn't you say in your post that each row in a table is one unique vendor?

               

              So how can you have 10 entries for one vendor?

              • 4. Re: Scoring by vendor based on attributes
                Deepak Rai

                {FIXED Vendor:AVG(If Country="Argentina" THEN 1 ELSE 0 END)}+{FIXED Vendor:AVG(If Service="X" OR Service="Y"   THEN 2 ELSE 0 END)}+{FIXED Vendor:AVG(If {FIXED Vendor:COUNTD(Legal Entity)}=2   THEN 2 ELSE 0 END)}

                 

                Try Above

                Thanks

                Deepak