This blog is mainly a self-reference on how to build a Pictogram Chart which I came across when doing a Workout Wednesday. I like how Pictogram Chart works as its a good way to show all the individual products without too much clutter.
For those that want to give it a go, you can attempt the workout Wednesday here: http://www.workout-wednesday.com/week-15/.
Without further ado, lets begin!
Firstly, we need to drag Sub-Category into the columns shelf and then Product Name into the Details. This way you will get each product to show up as an individual square just like the image below.
Next, we have to create a filter to show only the Top 5 Sub-Category based on the number of individual Product Names. Right-click Sub-Category then, create Set. Then we just need to select ‘By field’, Top 5 by Product Name, Count (Distinct). That way we get the Top 5 Sub-Category based on the number of different products.
X and Y Axis
Now that we have the foundation built, we now need to further refine it by building a X and Y axis. This is so we can have 10 squares or circles on a row. In this situation, I used a rank unique formula because I want each individual product to be assigned a number. I also did a ‘-1’ because the products are numbered from 1 on wards however the axis starts on a 0, meaning there will be a gap and we do not want a gap in this chart.
After you have created an X Axis and Y Axis calculated field, drag the X Axis into the columns shelf and Y Axis into the row shelf. Your chart should now look like its broken, don’t panic, its not broken! In order to get the chart working we have to right click the X and Y axis and use a table calculation: “For every Sub-Category calculate the ranking based on Product Names.”
If you completed the steps correctly, your chart should look like this:
To take it another step, we can split up the products to show 100 per group. So now that we have the Y axis, every row has 10 products, this means, row 0 – 9 is 100 products. So from row 10-19, we want to shift the row by 1 and then everything afterwards, we want to shift it up by 2. So its another calculated field and then you can replace the Y Axis with the new and improved Y Axis. But don’t forget, you will need to do your table calculation again.
We’re almost done! Now, we just need to add colour to the chart by grouping them into 3 groups: ‘1st 100, 2nd 100 and 3rd 100’. If the ranking is below 100, then its one group, and if ranking is between 100 and 199 then its second group, else its the third group. Drag the calculated field for colour in and don’t forget to do the table calculation if not the colour won’t appear.
Congratulations! You’ve now created a pictogram chart! I hope you had fun creating this. This is just the basic workout Wednesday challenge and you should give the Jedi challenge a go. 🙂