Comparing a Pivot Table cell to average of column / row ?

110 — Tue, 07 Feb 2023 14:43:15 GMT

Since I can't share real data, here's a mock-up.

Here's my raw data

Muppet	Episode	Songs
Bert	1	0
Ernie	1	1
Count	1	2
Elmo	1	1
Bert	2	1
Ernie	2	1
Count	2	2
Elmo	2	1
Bert	3	1
Ernie	3	1
Count	3	3
Elmo	3	5
Bert	4	1
Ernie	4	1
Count	4	2

As a final visualisation, I'd like to be able to have a pivot table, where I can see their performance vs the average, for themselves (ROW) and the episode ( COLUMN )

	vs Episode	vs Self	vs Episode	vs Self	vs Episode	vs Self	vs Episode	vs Self
	1	1	2	2	3	3	4	4
Bert	-1.000	-1.000	-0.200	0.333	-0.600	0.333	-0.250	0.333
Ernie	0.000	0.000	-0.200	0.000	-0.600	0.000	-0.250	0.000
Count	1.000	-0.111	0.600	-0.111	0.200	0.333	0.500	-0.111
Elmo	0.000	-0.571	-0.200	-0.571	1.000	1.143	-1.000	-1.000

So I've tried achieving this by trying to ignore the Muppet Name dimension in the code , but to no avail

{1 <EpisodeNum> } Songs

{1 <MuppetName> } Songs etc.

In Excel, it's very simple to have a parameterised sum/average like SumIfs /Averageifs etc. but Qlik's unique style of syntax is rather challenging.

Any suggestions would be greatly appreciated..

Re: Comparing a Pivot Table cell to average of column / row ?

hic — Wed, 08 Feb 2023 10:47:59 GMT

This is easy in Qlik. Using the "total" qualifier you can calculate any total in the chart:

Sum(Songs) will calculate for the specific cell - respecting both "Muppet" and "Episode".
Sum(total Songs) will calculate for the entire chart - disregarding all dimensions.
Sum(total <Muppet> Songs) will calculate the total per Muppet - disregarding "Episode".
Sum(total <Episode> Songs) will calculate the total per Episode - disregarding "Muppet".

Maybe you want Sum(Songs) / Sum(total <Muppet> Songs) ?

topic Comparing a Pivot Table cell to average of column / row ? in App Development

Comparing a Pivot Table cell to average of column / row ?

Re: Comparing a Pivot Table cell to average of column / row ?