Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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..
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) ?