How to create a Butterfly (Tornado) Chart in Qlik Sense

[ArturoMuñoz]

My previous post versed about what I think it is a very interesting use case of Set analysis element function P() to create dynamic sets and how to operate with those sets to solve really complex analysis scenarios in an easy and elegant way.

In the comments section of that post, I got a couple of questions regarding one of the charts we used in our web app, the so-called Tornado Chart. This post goes to answer that question by showing how to create a Tornado/Butterfly Chart.

Step by step guide:

Note: For the following example I got a sample data containing Population by Age data from the US census. It has 3 columns, Age Range, Male population and Female population.

1. Drag and drop a bar chart to your sheet.
2. Add a dimension.  I’ll add Age Range to mine.
3. Add two expressions. I need one expression to display female’s population and a second one for males.
   
   At this point, my chart looks like this:
   
   
   
   
4. Next, we will adjust the bar chart sorting and appearance.
   
   Sort the chart according to your data. For this data set my best option is to sort Age Range using load order, to do so, move Age Range to the top and then uncheck all the sorting options to sort on load order.
   In the Appearance section of the chart property panel, switch Presentation to Horizontal and Stacked and set Value labels to Auto.
   
   
5. Now we need to figure out how to “move the y axis to the center of the chart". To do so I’ll transform the data to make Female population negative by changing my expression to: Sum(FemalePopulation)*-1
   
   
   
   
6. My chart looks almost right but there’s still a detail that doesn't fits well in the picture above. Note that the value labels for Female Population are displayed as negative numbers. While technically correct, it doesn't work for this chart, I need it to be displayed as positive vales as well.

As a general note remember we can set up the format for positive and negative values for any expression. Format argument in the Num function can take 2 parameters, first one indicates how to format the number if it is >0 and the second piece if number <0.

=num(sum(x), 'postivenumberFormat;negativenumberFormat')

As I want negative and positive number to look exactly the same I could use the following format arguments:

num(sum(FemalePopulation)*-1,’#,##0;#,##0’)

Note: Please be aware of some potential issues when exporting the chart as data since the resulting file could contain negative values.

Enjoy it.

[robin_heijt]

As the pictures are showing, he has the genders set as measures. But I might be interpreting this wrong then.

Anyway, he has separate measures for both male and female. I have my genders in 1 column, would you possibly be able to explain me how to achieve this with gender as 1 dimension?

[anderseriksson]

Gender is the dimension which separates the measure (number of people) in positives and negatives.
Technically it is done in the measures.

Actually don't understand how you would like your butterfly to work?

[robin_heijt]

Okay, I am starting to get what you're saying.

I would like to have the exact same butterfly chart, however I do not understand how I am able to split my gender dimension into negatives and positives.

Could you tell me what expression to use if my dimension is: Gender (Male-Female)

[anderseriksson]

Male: Sum({$<Gender={'Male'}>} Population)

Female: -Sum({$<Gender={'Female'}>} Population)

I prefer doing the dimensional split with set analysis instead of if-statements as this uses the assosiative engine much more efficient by selecting a subset of data and do a simple sum on that while the if-statement does logik on every data row without the help of the assosiative engine.

[robin_heijt]

Thank you very much, that worked!!

[robin_heijt]

One more issue that I am running into now, is that both bars seem to represent the exact same value.

I already changed Sum to Count as that is the aggregation I need. However in both scenarios both female and male seem to represent the exact same values.

Would you have any idea what might cause this?

[anderseriksson]

Not unless you show your expression as it sounds you made some error in it.

[robin_heijt]

This is what I use.

Dimension 1: [Time in Company]

Measure 1: -Count({$<Gender={'Female'}>} [Global ID])

Measure 2: Count({$<Gender={'Male'}>} [Global ID])

And as you can see in this picture is that both measures add up to the same value.

[anderseriksson]

Looks like an error in your data modell then.
Like Gender is uncoupled in regard to [Global ID].

[robin_heijt]

You are indeed correct, I accidentally linked it to the wrong gender file.

Thank you very much for taking the time to explain the process and the troubleshooting.

Have a nice day!