CODEWITHSUNDEEP
business-intelligencebusiness-objects
mf.cummings
mf.cummings

Reputation: 315

SAP BO Webi 4.1 Create Variable that returns the value of another variable

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, StartTime and EndTime 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 ShiftXYZ. 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

Upvotes: 0

Views: 2842

Answers (1)

Joe
Joe

Reputation: 6827

There are a couple of ways to do it. Here's one:

=Sum(If [Code] InList ("BREAK1";"BREAK2") Then [TimeDiff] Else 0)

Upvotes: 0

Related Questions