topic Set Analysis behaviour in pivot table in QlikView

Set Analysis behaviour in pivot table

pdumas — Tue, 29 Dec 2009 05:00:18 GMT

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.

Set Analysis behaviour in pivot table

Tue, 29 Dec 2009 05:29:16 GMT

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,' ')

Set Analysis behaviour in pivot table

pdumas — Tue, 29 Dec 2009 06:17:50 GMT

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.