Making a population pyramid chart - specify total for %, and reverse axis direction
Hi all, i need to be able to create these charts. Percent of total, in agebands split by sex, displayed as a bar chart with one of the sexes using an inverted x-axis. I've made a table easily of course:
And then created a "% of total" 'show as', here you can see that female aged 80+ make up 1.75% of the full population, all cool so far:
And the next step convert that into a bar chart, this leaves sex in categories and gives me sex split % for each ageband, so i moved sex to Trellis Horizontal and get closer:
But on closer inspection the % values are now actually being calculated against the total per sex, instead of the grand total. So that is question 1, how can i specify which total the "show % of total" is based on please?
And the second probably predictable question, is how can i invert one of those x-axes please to finalise the chart? in Tableau i used to multiply values by -1 before making the calculation but discovered i could invert an axis direction instead.
Sorry if this is answered elsewhere, the nature of searching for a "pyramid chart" meant lots of answers :)
11 replies
-
Hi Adam,
The % of Total created from the slide off is what we call a "Context" calculation, i.e. it is performed in the context of the query, rather than against the model. The reason it is showing % for Male and Female separately is because a Trellis operation effectively creates two queries, % is calculated in the context Male and Female separately.
"Semantic" calculations are defined against the model and are evaluated based on that rather than than the context of the query. Read this blog and watch the video for a full explanation of Pyramid's calculation engine. https://community.pyramidanalytics.com/t/x1drda/pyramids-calculation-engine-explained
So we need to create a % calculation where the divisor is "locked" to be the Total population and the numerator is free to "float" and assume any value that is shown in the grid.
For this, you need to use the Fx button in the Measures panel and create a calculation using Formulate.
Drag or double click a Data Point to the canvas, then select your Population measure. In my case I am using a Count of the attribute Gender. Also select Male from the Gender Attribute. Insert a division symbol, then drag another Data Point Block after the division symbol. Again select the same measure, but also select the "All" check box against the Age Range and the Gender attributes.
Set the format to be % and save as % Male. Duplicate the Measure from the tab menu, change the numerator Gender attribute value to Female. and save as % Female. Unfortunately, there isn't the option to reverse the axis, so you will have to use the same technique as you did with Tableau to reverse the data series by multiplying the Female values by -1.
In your Chart, select the two calculated measures and place the Values chip in the Trellis Horizontal drop zone. You should then get what you are after:
Hope that helps.
Ian
-
A Tornado chart is under development. It will be out soon and it will make this process much simpler.
-
Excellent thanks Ian, will work it through. And that's good news Avi too.
-
Hi Avi, do you have a rough estimate for when Tornado charts will be released please? Need to plan around that if I can. thanks
-
We will try for the next release.
-
-
Hi Adam,
Yesterday we pushed the latest Patch release for Pyramid. Good news is that we have included Tornado charts so all our messing about re above has now gone away! Yay!
To use it in your scenario, create a calculated measure of % MF as below:
Then create a Tornado chart below, Age Range in Categories, % MF as Measure and Gender in Color:
This should address all the issues you have raised above.
Have a go and LMK if any issues.
Hope that helps.
Ian
-
OMG great news! may have to wait a bit for the update to be applied here but i will push for it
-
Hi Ian, I’ve not been able to try this for a while as our local install was no up to date. So today I’ve looked on Partners.Pyramidanalytics to see if can use Tornado there instead, and I don’t see the Tornado chart.
Partners.P looks to be at version 2020.10.291 and its been a few weeks since you mentioned Tornado, do you know if the version is too low perhaps? Or has the chart been removed?
Cheers!
-
Hi Adam,
The Tornado chart was included in the 2020.11 release. It seems, for some reason, somehow, that the partners server has escaped the upgrade process!
I'll get that actioned and updated to the latest 2020.12 release. I would expect it to be resolved by beginning of next week, so check back then.
Thanks for drawing our attention to this.
Regards,
Ian
-
Ah cool thanks Ian :)