Using dimensions on the X and Y axis and plotting measures at the intersection is a useful visualization. The out of the box solution in QlikView is the Grid chart.
The grid chart does have limited options for representing data.
The Pivot Table can be a more robust alternative. A Pivot Table grid (Crosstable) is created by dragging one of the dimensions to the horizontal position.
Now we can represent the measures as a heatmap by coloring the background of the cells. This is frequently a more effective visualization than circles, .
We can also display numbers in the cells.
We can display multiple expressions per dimension.
Here I show both the number and a linear gauge allowing for clear comparisons between Regions. The user can swap the dimensions if desired.
You can mix colors and numbers to show interesting patterns. Here’s a map of average Sunrise/Sunset/Daylight for my home town.
Maybe it’s not a design winner, but I find it interesting.
We can blank some cells in the grid to make meaningful shapes, in this case a rough outline of the United States.
All of the examples shown can be downloaded here.
A few styling tips:
- Style format “Clean” is a good starting point for managing border lines and spacing.
- “Custom Format Cell” Border before/after (double line) can be used to create white space between rows as shown in the Minimum Wage example above. Also, setting Text Size to other than the default 100% can be useful.
Do you have any examples of Pivot Grids to share?
-Rob
I have used pivot tables to create gantt charts, not that unique i know but one example used by many more in the community.
I love the US states example, really nice idea!
Credit for the US States map idea to https://twitter.com/PostGraphics/status/654113991732563968
I used this kind of object in retail applications (or documents qv) to display with “traffic light” which branch had sent their data to the central Dababase.
Generally this kind of businesss work offline from central host and data is sent in a asynchronously manner, so the traffic ligth is a quickly way to show what data is showing in de QV apps.
Very interesting article. Thank you.
On the light-hearted side, Ai Weiwei could use it to protest Lego.
http://www.cnn.com/2015/11/09/arts/ai-weiwei-instagram-lego-protest/index.html
It’s also probably the closest native way to do a calendar heat map. http://bl.ocks.org/mbostock/4063318
What would the max resolution of a pivot table grid be? 100×100?
The map is a great idea. I never would have thought of that.
Karl, I don’t know of a specific limit on the grid size. I modified my Daylight chart to have 365 dates across the top and 1440 minutes on the left and it rendered fine. That’s 535k cells! Not a particularly useful viz, and probably better plotted as a Scatter Plot.
Like the USA map!!!
Rob
I would like to thank you for putting these together; they were very helpful.
I did have a question regarding the heat-map. If you go to the ’presentation’ properties tab and check off ‘Show Partial sums’ for the days(row) and times(column) the text color mix will carry over and the majority of these totals will not be visible. Is there a way to adjust the color-mix coding to show these partial sum totals as just black()?
Best
Rick
Hi Rick,
I’m not following. When I check “Show partial sums” for both dimensions the heat block and the text show up just fine. The comments section doesn’t allow for pasting graphics. If you have a different result, contact me on the Contact form on this site and I’ll arrange to look at the image.
Rob
I uploaded them temporarily to the following website address so that you can see what I am observing the partial sum changes.
http://www.mandecsys.net/images/Rob1.jpg
http://www.mandecsys.net/images/Rob2.jpg
Thanks for you assistance.
Rick
Rick,
You can conditionally format by detecting the Totals with Dimensionality() (total row) or SecondaryDimensionality() (total column). So modify the Text background to look like this:
if(Dimensionality()=0 OR SecondaryDimensionality()=0, black(), //otherwise
The same can be done for the Background color to force a specific color. Or the same colormix scheme can be carried over in the total rows by modifying the aggr() to use only one dimension on the total rows.
Rob
That did it, thank you so much!
Best
Rick
Using the pivot instead of the grid chart means you can have a larger dataset as the table can scroll where the grid chart would just use smaller dots
Love the cartograph of the US btw 🙂
Nice one. Thank you Rob
Hi Rob
This is excellent. Especially the US States map.
We no need to bother about Google API calls, Latitude, Longitude, boundary details. With just the X and Y axis grids, it does the magic and the rest will be taken care by Custom format Cells.
Thanks
Satish