topic Summing up Max in QlikView

Summing up Max

Thu, 01 Dec 2011 19:07:31 GMT

Hi All,

I have a somewhat unique challenge with data provided by a report I am stuck with for now. The data concerns login times for agents in a phone system. The agents can be in multiple phone queues. Unfortunately, the login times that the system reports is the whole time for each queue in each row:

Queue	Call time	Login time
Queue 1	2:34	9:00
Queue 2	1:23	9:00
Queue 3	4:00	2:00

I can sum the call time and get what I want for a day, or a range of days. But in order to get the login time for a day, I have to take the max. (It is assumed they are in one queue all day, thankfully).

My question is, what do I do for data ranges greater than a day? The summing of the call times works fine, but I will get the maximum login time for whatever day in the range happens to be largest. I want the sum of the maximums.

Summing up Max

Thu, 01 Dec 2011 19:37:14 GMT

Clarification: I can get this to work in straight table by adding the user and date as expressions, using max as the definition of login, and using sum in the total mode.

I want to make a pie chart showing the distribution of times for each activity: call times, lunch, after call work, etc. I need the login time so I can calculate the idle time - login minus everything else. The pie chart won't have the dimensions, so I can't figure out how to get a pie chart to work for more than one day and one agent.

Is this possible?

Re: Summing up Max

johnw — Fri, 02 Dec 2011 00:30:07 GMT

I'm not sure quite what you're saying about the pie chart. But a sum of maximum login times by user and date would be something along these lines:

sum(aggr(max(LoginTime),User,Date))

Summing up Max

Fri, 02 Dec 2011 00:56:28 GMT

What I would like is a pie chart that shows the percentage of time the agent spent during each activity. One of the slices will be 'idle' time when they aren't doing anything but waiting for the next call. I need the login time so that I can subtract the things they did to get that idle time.

Your expression works great in a straight table, but if I have those dimensions in the pie chart, I don't get what I want. Unless I am missing something with pie charts.

I may have to live with the straight table for aggregations and use the pie chart only for one day and one agent.