Set Analysis behaviour in pivot table

[pdumas]

Hello,

The file has 3 columns : Agent-ID, CallPickedUpTime, CallFinalTime

I need to find the "Agent_ID"s of agents on call and list it over time.

To prototype the set analysis syntax, I have added a variable (vTemp) and display the result in a text box :

concat({$<CallPickedUpTime={"$(='<'&'$(vTemp)')"},CallFinalTime={"$(='>'&'$(vTemp)')"}>} Agent_ID,' ')

It works perfect.

To get the list over time, I have built a pivot table with

- as dimension : a timestamp, from a timeline table

- as expression :

1- concat({$<CallPickedUpTime={"$(='<'&column(0))"}, CallFinalTime={"$(='>'&column(0))"}>} Agent_ID,' ')

2- concat( Agent_ID,' ')

Both expression give the same result : the agent that picked a call at the timestamp reported in the dimension. Calls picked at previous timestamps are ignored.

Why?

How can I get the expected result in a chart?

Thanks

Pierre.

[#2]

I'm not completely following your post, but it sounds like you are trying to ignore the dimension. In order to ignore the dimension in an aggregate function, use the TOTAL modifier: Sum(TOTAL Sales).

Try:

concat(TOTAL {$<CallPickedUpTime={"$(='<'&column(0))"},
CallFinalTime={"$(='>'&column(0))"}>} Agent_ID,' ')

This should give you a list of all Agent_IDs in the data set:

concat(TOTAL Agent_ID,' ')

[pdumas]

With your suggestion, I get a chain with all Agent_IDs, which is better,

but I want only the Agent_IDs where the CallPickUpTime is before the timestamp in the current dimension and the CallFinalTime after timestamp in the current dimension.

eg : Data :

Agent ID, CallPickUpTime, CallFinalTime

1, 10am, 10:12 am

2, 10am, 10:09 am

Table

10am / 1 2 (agents 1 and 2 on call)

10:10am / 1 (agent 1 on call)

10:20am / - (no agent on call)

Thanks

Pierre.