Chart Pivot Table - Help with Expression

angmarceau1971 — Wed, 25 Nov 2020 16:16:04 GMT

I'm new to Qlikview and really new to Set Analysis and I'm struggling getting this working. Below is a screen shot of my Chart Pivot Table I'm trying to create.

I'm having difficulties with two columns.

% of Total Day - I need to have the % be a percentage of the day and not the entire Total Calls.

So 2020/10/21 should have the following:

Date	Menu Option	Total Calls	% of Total Day
2020/10/21	Option 1 - Concierge	890	34.50%
2020/10/21	Option 1 - Concierge - After Hours	25	0.97%
2020/10/21	Option 2 - Solutions	1140	44.19%
2020/10/21	Option 2 - Solutions - After Hours	30	1.16%
2020/10/21	Option 8 - Extension Dialing	485	18.80%
2020/10/21	Option 8 - Extension Dialing - After Hours	10	0.39%

% Match - This one works fine when I have the Pivot Date expanded, but when I collapse the date, it goes to 0%.

Here is what I have in the Chart Pivot.

Dimensions -
1) Date
2) MenuOption

Expressions-
1) Total Calls = Sum(Calls)
2) % of Total Day = Sum(Calls)/Sum(TOTAL Calls)
3) ANI Match = Sum(CCMatch)
4) % ANI Match = If(Match([MenuOption],'Option 1') OR Match([MenuOption],'Option 1 - After Hours'), sum(CCCMatch)/sum(Calls), 0)
5) Request Next Agent = =sum("sum(if([CCMatch]=1,CCRoutedToS))")

any help would be appreciated.

Re: Chart Pivot Table - Help with Expression

edwin — Tue, 27 Oct 2020 17:51:08 GMT

try this for total%

=sum([Total Calls])/aggr(nodistinct sum([Total Calls]), Date)

Re: Chart Pivot Table - Help with Expression

angmarceau1971 — Tue, 27 Oct 2020 20:51:35 GMT

That worked perfectly when the pivot table is expanded to see the menu options. But when not expanded, i'm seeing 1e+002%

Re: Chart Pivot Table - Help with Expression

angmarceau1971 — Wed, 28 Oct 2020 10:47:58 GMT

Figured it out. Just needed to format as Integer instead of Number and Show in Percent.

Thanks for all your help!

topic Re: Chart Pivot Table - Help with Expression in QlikView

Chart Pivot Table - Help with Expression

Re: Chart Pivot Table - Help with Expression

Re: Chart Pivot Table - Help with Expression

Re: Chart Pivot Table - Help with Expression