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

# Distinct sumif

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 ID Type [Duration] 1234 Break Time 7 1234 Break Time 5 5678 Break Time 17 5678 Break Time 7 5678 Break Time 5 5678 Break Time 13
• ###### 1. Re: Distinct sumif

Hi, Venkatesh

Hope this could help

ZZ

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

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

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