I have a narrow dataset: AgentID, Name, Date, Code, StartTime, EndTime. I created a Detail variable titled "TimeDiff" that returns the difference between StartTime and EndTime as an integer representing the minutes between the two timestamps.
For each date, each agent has at least 6 rows, where the Code, Start/End times reveal Agent scheduled activity.
Example:
AgentID | Name | Date | Code | StartTime| EndTime | TimeDiff
Agent123 | Bob Jones | 01/01/2016 | ShiftXYZ | 07:00:00 | 15:00:00 | 480
Agent123 | Bob Jones | 01/01/2016 | BREAK1 | 09:00:00 | 09:15:00 | 15
Agent123 | Bob Jones | 01/01/2016 | LUNCH | 12:00:00 | 12:30:00 | 30
Agent123 | Bob Jones | 01/01/2016 | BREAK1 | 14:00:00 | 14:15:00 | 15
Some times are additive, like Shift. Some are subtractive, like BREAK1, BREAK2, etc. (there are 48 unique codes in all). In another report, I would like to sum the TimeDiff for each group of codes. I've tried a few different approaches, such as creating a measure with the formula:
=Sum([Variables].[TimeDiff])Where([Code] Not In("ShiftXYZ"; "ShiftABC"))
=If [Code] InList("BREAK1";"BREAK2") Then Sum([Variables].[TimeDiff]
and various permutations of each approach. I get lots of different error messages but not the output I want.
Can anyone suggest either the outright answer or suggest a better approach? I suspect I'm missing some element. I would appreciate any suggestions.
--mfc
