Multiple Grouping with conditions

[george55]

Hi all,

Who can help?

Conditions:
1. Group1 (CallId): make a group segmented by CallId (in this example: 115, 117)
2. Group2 (Team):  make a group segmented by Team (in this example: B, K , A)
3. Condition grouping Group2 (Team): Find the team with the highest EventID (result Team 'A')
4. Condition within Group2: Find the highest EventID with EventType 'TALKING' and take its EventDuration time
5. Result schuld be: Team and its Talkingduration (EventDuration)
     

Team	Talkingduration
A	1:52

the data is attached as well as Excel:

CallId	EventId	EventType	Team	EventDuration
115	1	AUTO_ATTENDANT	B	00:00:04
115	2	RINGING	B	00:00:05
115	3	TALKING	B	00:02:37
117	1	AUTO_ATTENDANT	B	00:00:04
117	2	RINGING	B	00:00:05
117	3	TALKING	B	00:02:37
117	4	TRANSFER_HOLD	B	00:00:24
117	5	DIALING		00:00:00
117	6	QUEUE	K	00:00:00
117	7	28	K	00:00:00
117	8	RINGING	A	00:00:09
117	9	TALKING	A	00:00:16
117	10	TRANSFER	A	00:00:00
117	11	TALKING	A	00:01:52
117	12	RECEIVING_DROP	A	00:00:00

thanks for any help.

George

[dwforest]

You can use Set Analysis:

Condition grouping Group2 (Team): Find the team with the highest EventID (result Team 'A')

Max({<EventID={Max(EventID)}>}Team)

4. Condition within Group2: Find the highest EventID with EventType 'TALKING' and take its EventDuration time

Max({<EventType={'TALKING'}>}EventID)

5. Result schuld be: Team and its Talkingduration (EventDuration)

Table with Team, (the above 2 measures), EventDuration

[george55]

Have some problems with your statement:

Max({<EventID={Max(EventID)}>}Team) is not working. Maybe you mean: 

Max({< EventId = { '$(=Max(EventId))' }>} Team)

Trying to put this information nested together, but dit not work. What is wrong?

Sum(
{<
EventId = {'$(=Max({< [EventType] = {'TALKING'}>} EventId ))'}, [EventDuration]
>}
[Team] )