4 Replies Latest reply on Oct 11, 2018 3:55 AM by Ramya Nagaraju

    Calc to Return a Majority

    Elie Rahi

      I have data that shows each shift done by each person along with their shift type. Some workers do a variety of different types of shifts. I need a calc that works out for each person which shift type is mostly performed. So lets say one person does the following 6 shifts with these types of shifts:

       

      Type 1

      Type 2

      Type 2

      Type 1

      Type 3

      Type 1

       

      Then I want the calc to return Type 1 for that person because that's the majority.

       

      Spreadsheet attached

        • 1. Re: Calc to Return a Majority
          Ken Flerlage

          I'd start by creating a calculated field that gets the number of occurrences for each person for each shift type.

           

          Person Shift Count

          // Get the number of occurrences of each shift for each person.

          {FIXED [Person ID], [Shift Type]: SUM([Number of Records])}

           

          Then create a calculated field that gets the maximum of the above, for each person:

           

          Max Overall

          // Get maximum number of shifts for each person.

          {FIXED [Person ID]: MAX([Person Shift Count])}

           

          Now create another calculated field that compares each shift's shift count to the max and returns the shift where they match:

           

          Max Shift

          // See if this shift has the same count as the maximum of all shift counts.

          // If so, we want to keep this one. Otherwise, we'll just return NULL.

          // But, we could get multiple shift types with the same number of shifts.

          // So, we'll use MIN to get the first of these.

          MIN(

          IF [Person Shift Count]= [Max Overall] THEN

              [Shift Type]

          END

          )

           

          I think that should do it for you. See attached workbook.

          • 2. Re: Calc to Return a Majority
            Shinichiro Murakami

            Hope this helps.

            Couple of layers of LOD are needed.

             

             

            Step by Step

             

             

             

             

            In this logic, if the score is tie, picking smaller number of Shift type.

             

            Thanks,

            Shin

            • 3. Re: Calc to Return a Majority
              Ken Flerlage

              Great minds think alike!!

              • 4. Re: Calc to Return a Majority
                Ramya Nagaraju

                Hi Elie,

                 

                I've a different solution. I first find the max number of Type for each person Id, then find the corresponding Type name. If there are more than one Type with the max value, this viz will list all the Types.

                 

                screenshot1.png