3 Replies Latest reply on Jan 3, 2018 2:21 AM by Venkatesh Raju

    Distinct sumif

    Venkatesh Raju

      Hello Experts,

       

      Looking for some help here with distinct count of staffs who have breached the Break time as 2 categories "Less Break" and "excess Break".

       

      A staff would have taken multiple breaks in a day, if the sum of duration is <=15 it should be "Less Break" and >= 90 "excess Break"

       

      Below the data sample format for easy reference.

       

         

      Staff IDType[Duration]
      1234Break Time7
      1234Break Time5
      5678Break Time17
      5678Break Time7
      5678Break Time5
      5678Break Time13
        • 1. Re: Distinct sumif
          Zhouyi Zhang

          Hi, Venkatesh

           

          Not seeing your actual data, but based on your sample data, please find my solution attached.

           

           

          Hope this could help

           

          ZZ

          1 of 1 people found this helpful
          • 2. Re: Distinct sumif
            Simon Runc

            hi Venkatesh,

             

            So one way to do this is to use LoDs. This has the advantage that the "dimension" we create based on their total break-time, can be used without needing the StaffID in the Viz.

             

            So first I created this LoD to get the total breaktime per staff

             

            [Total Break per Staff - LoD]

            {FIXED [Staff ID]: SUM([Duration])}

             

            NB you may need to add other dimensions to the LEFT of the LoD, say Week Number, if you wanted to assess each Staff member per week, rather than over all their breaks

             

            Once we have this we can use it in a logic calculation to get the correct classification for each StaffID and use COUNTD (so the count is distinct)

             

            As your example data only had 2 employee's over which neither had >90 mins I used a parameter to set the the treshold (currently 30, so one person is under and one over)

             

            [Staff - Break Class]

            IF [Total Break per Staff - LoD] <= [Break Threshold] THEN 'Under'

            ELSE  'Over'

            END

             

            I also only see if you are over or under...you could do something like this (in your real dataset)

             

            IF [Total Break per Staff - LoD] <= 15 THEN 'Under'

            ELSEIF [Total Break per Staff - LoD] >= 90 THEN 'Over'

            ELSE 'OK'

            END

             

            Hope that helps, and makes sense

            1 of 1 people found this helpful
            • 3. Re: Distinct sumif
              Venkatesh Raju

              Thank you friends, this is one of the best forum. the above help was great and very useful to me.