topic Re: Question about TOTALs over fields / aggregation in QlikView

Question about TOTALs over fields / aggregation

Tue, 19 Jun 2012 08:42:30 GMT

Hi,

I'm going round in circles here and hope someone can help

I have a pivot table that has 3 dimensions

Week

Agent

[Contact Outcome] * this is a drill down group

The table shows the percentage of call results falling in each contact group for each agent per week, and this works fine, I use the following expression

COUNT({$<DMC = {'True'}>} callResultId)/COUNT({$<DMC = {'True'}>} TOTAL <Week, AgentName> callResultId)

However, when I drill into the contact outcome drill down group, it shows the percentage of call results in the week for the agent within that Contact Outcome group. What I want it to do is show the count of the lower level field in the group as a percentage of ALL the calls for that agent for that week.

Can anyone advise where I'm going wrong?

Thanks

Re: Question about TOTALs over fields / aggregation

Anonymous — Tue, 19 Jun 2012 12:48:20 GMT

You will need to add some Set Analysis in the denominator portion of your expression to release selection on each field included in the drill down group.
For example, if the fields in the group are [Category], [Brand], and [Product], the expression would look as follows:
COUNT({$<DMC = {'True'}>} callResultId)
/
COUNT({$<DMC = {'True'}, [Category]=, [Brand]=,[Product]=>} TOTAL <Week, AgentName> callResultId)

If you need to only release selection in the current level of the drill down, this will require some logic to determine what level you are at - basically checking for 1 possible value starting at the lowest level of the hierarchy.
-Phil

Re: Question about TOTALs over fields / aggregation

Tue, 19 Jun 2012 12:59:38 GMT

Fantastic, works a treat... thanks