Sorting it out with dual()

Sarah Habershon
June 16, 2017

In my first Qliksense blog, I announced that I was taking on a project to focus my learning process, and I’m going to share a few of the useful things I learned along the way as I constructed my first demo app.
The data I used to build my app comprised a cohort of service users, and I wanted to create a population pyramid to

show the distribution of the cohort across age groups by gender. I sorted the cohort into age groups in the data load editor, using five-year groupings.
To build the population pyramid, I used a bar chart. As each individual in the cohort was assigned a unique ID, I counted these IDs to form the Measure. I assigned the males in the population negative values, like this:

Count(If(Gender = ‘Male’, ID))*-1

By choosing the horizontal and stacked presentation options for the bar chart under the Appearance menu, I produced a stacked population pyramid with a central axis.

But there was a problem: my age brackets weren’t organising themselves into the correct order. The 70+ age bracket insisted on plunking itself down in the middle of the axis, where it had no business being, and the others weren’t self-organising effectively either. I realised that this was because I had defined the age groups in the load script as strings, not numerical values, so Qlik didn’t know what order to put them in..
The solution is a nifty function: dual().
Dual lets you assign a second, numerical value to a field, so that the field can be sorted according to that second value. This is great for sorting axes of discrete or categorical values. For instance, if you had chart showing counts of events for each day of the week, you could use dual() to make your weekdays organise into the correct order on the axis, instead of alphabetically, which wouldn’t be the best way to present the data.
Here’s how I used dual() to sort my age group axis appropriately.

After I’d reloaded the data with the dual() function in place, the population pyramid stacked just how I wanted it.

Sarah blogs about how data can be made aesthetic as well as informative.

Copyright © 2019 OptimalBI LTD.