Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Question about TOTALs over fields / aggregation

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

1 Solution

Accepted Solutions
philbishop
Contributor

Re: Question about TOTALs over fields / aggregation

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

2 Replies
philbishop
Contributor

Re: Question about TOTALs over fields / aggregation

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

Not applicable

Re: Question about TOTALs over fields / aggregation

Fantastic, works a treat... thanks

Community Browser