Twisting the knife in a useful pie chart

Some people think that pie charts will kill kittens and bring the downfall of western civilization. Some people (**deep breaths, be nice, serenity now, woosah**) love pie charts. Since my self control is only so strong, we won't mention 3D pie chart 'people'.

Everyone knows that I'm firmly in the first camp, but I'm nothing if not flexible and tolerant of other people's stupid viewpoints. Which brings me grudgingly to the fact that I've recently read a couple of posts that point out good uses of pie charts (Jorge Camoes & Francis Gagnon). The gist is that pie charts are good for part to whole comparisons. Please notice that 'part' is singular, not plural. Let's apply this to Qlikview.

A typical pie chart will show each slice of the pie as a different color.

Your user is entertained with the pretty chart and can pretty easily see that the United States has a little more that half of the total. But what if they want to compare the United States and Australia to the whole? Or Germany and the United Kingdom to the whole? To fix it, we are going to only use two colors and group the 'active' slices. And while we're at it, we'll add a text box that gives you the actual percentage of the whole.

 Step One

Create your chart. Unlike my normal advice to limit a pie chart to a maximum of four or five slices, it really doesn't matter how many this time.

Step Two

Open the expression attributes, and put the following expression as the Background Color:


     =if(match([Country],concat(distinct [Country],',')),
       Green(), ARGB(150,225,225,225))

This express will look at which values of the field [Country] are selected and make them green. It's the same technique we used previously with a line chart.

Step Three

On the Sort tab, check 'Expression' and enter the following into the expression editor:


     =if(match([Country],concat(distinct [Country],',')),
       1,2)

This express will look at which values of the field [Country] are selected and give them a value of '1'. The unselected with get a value of '2'. It now will sort numerically and 'group' the selected values.

Step Four

Go to the Presentation tab and add a Text in Chart.

In the Text expression editor, add the following expression:


     =If(GetSelectedCount([Country]) = 1,
       GetFieldSelections([Country])
       &
       ' Accounts for ' & Num((Sum([SalesAmount])/Sum({<[Country] = >} [SalesAmount])),'##.#%')
       &
       ' of Total',
       If(GetSelectedCount([Country]) > 1,
       GetFieldSelections([Country]) & ' Account for '
       &
       Num((Sum([SalesAmount])/Sum({<[Country] = >} [SalesAmount])),'##.#%') & ' of Total'))

This ugly expression looks at the number of selections in [Country], properly formats the text and gives the 'part' percentage for the selections.

Step Five

Hold down Ctrl+Shift and move and size the various parts of the chart. Format to taste.

In honor of the Academy Awards, who should definitely invite me to walk the red carpet, I've recorded FortuneCookieBI's first video.

I'd like to thank my family, the Academy, Friendster, Nutella, and Will Smith for making this possible. Without the smooth sounds of the Fresh Prince or the hilarity of Hancock or the horror of I Am Legend, none of this would have happened.