topic Distinct Count but Sum Total in QlikView

Distinct Count but Sum Total

Mon, 23 May 2011 09:42:42 GMT

Hi All,

I was busy creating a test QV document to load as I couldnt find a way to get to the answer I was looking for. When creating the document I managed to figure a way out but it looks messy and Im wondering if there is a better way to do this.

I have attached the QVD with an inline list of what my data looks like.

The Question:

----------------------------------

Agents are logged onto multiple skillsets simulataneously, the logged on time for each skill set would be the same because of this. I want to know what the accumulated logged on time is for an interval of time for all agents logged in. Taking into account that I need the (MAX) logged on time for each agent, not the combined for each agent for each interval.

Cheers,

Byron

Distinct Count but Sum Total

yasus — Mon, 23 May 2011 10:37:15 GMT

I'm not sure, if I have understood you right but maybe the following formula is what you are looking for?

Distinct Count but Sum Total

Thu, 26 May 2011 08:20:02 GMT

Thanks, this did get me the same answer. Do you mind elaborating a bit on what the aggr function actually does. I tried to use this in my live report and it did not aggregate correctly.

Thanks for your assistance in this regard

Cheers,

Byron

Distinct Count but Sum Total

Thu, 26 May 2011 08:32:17 GMT

Hi Byron,

Aggregation function helps to sum the data based on Dimensions.

In a setanalysis expression, two sums cannot be used

Ex: sum(sum(...)

so in your example

sum(aggr(max(LoggedOnTime),AgentName))

gives the sum of ( sum of maximum of LoggedOnTime based on dimension AgentName)

Hope that helps

Sravan

Distinct Count but Sum Total

Thu, 26 May 2011 08:49:26 GMT

That expains it very nicely. Thank you Sravan