Qlik Community

Ask a Question

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

ArturoMuñoz
Employee
Employee

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:

    5.png

  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.

    6.png
  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

    7.png

  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’)

 

8.png

 

 

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

 

Enjoy it.

Tags (2)
20 Comments
beck_bakytbek
Master
Master

Hi Arturo, it does look great, thanks a lot for sharing

4,522 Views
mdmukramali
Specialist III
Specialist III

Dear Arturo munoz,

Nice post thanks for sharing.

I have used in an application which is working fine.( Using Qlik Sense September 2017 version)

but I have one comment:

Number format must be applied for Postive expression (First Measure in our Application).

(else we can apply for both expressions)

Number Format.PNG

Number Format1.PNG

Thanks,

Mukram.

4,522 Views
sahadevpatil
Contributor III
Contributor III

Nice post thanks for sharing.

4,522 Views
luismadriz
Specialist
Specialist

Nice, many thanks!

0 Likes
4,522 Views
saniyask
Creator
Creator

Hi Arturo,

Thanks for the post !!! It was a great learning.

Keep up the good work.

Saniya.

0 Likes
4,522 Views
sergeyay
Contributor III
Contributor III

Hello,

It's usefull information.

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’)

Will this formating ’#,##0;#,##0’ work for QlikView?

Sergei

0 Likes
4,522 Views
ArturoMuñoz
Employee
Employee

Hey Sergei,

Formatting like described works in QlikView as well, yes

3,710 Views
sergeyay
Contributor III
Contributor III

Hello,

I've got it. Number format must be applied for first Measure. Mohammed Mukram Ali was right.

But how did you make positive a horisontal axe?

Sergei

0 Likes
3,710 Views
robin_heijt
Creator
Creator

Thank you for sharing this!

I am just wondering if this would also be possible with 2 dimensions and 1 measure?

If so, could you please explain?

I have:

Dimension 1: Age group

Dimension 2: Gender

Measure:Count of population

I would like to have the same result, however with gender as a dimension.

Thanks in advance.

Best,

Robin

0 Likes
3,710 Views
anderseriksson
Partner
Partner

Two dimensions (age & gender) and one measure (number of people) is exactly what Arturo is showing in his initial example.

0 Likes
3,710 Views