Qlikview Activity Ribbon Chart
What better way to start a new blog than with a new Qlikview chart type (its ‘new’ in the fact I’ve never seen it before in Qikview)?
After seeing the following chart via the wonderful FlowingData.com blog (http://flowingdata.com/2012/01/11/vehicles-involved-in-fatal-crashes/) about fatal car crashes in the US – yes it’s a grusome subject but it’s interesting I thought the natural thing: ‘surely that can be done in Qlikview’…and it can.
Surely that can be done in Qlikview?
Before I show my Qlikview version I’ll quickly outline why I really like this graphic. For me it simply and quickly trasnfers the information it’s meant to; when fatal car accidents tend to happen both seasonally and during the week – you can easily see that more acidents happen at the weekend. I can’t imagine that Qlikview is used too often to analyse fatalities (I’m sure it is in the medical space) but this chart is useful for anything that has daily activity; sales, volumes, costs, calls, activity etc etc, so with that in mind I’ve itegrrated the chart into a Qlikview Server Monitoring Application (More on that in a later post) to show daily server session activity:
So there you have it; we can see that the server is getting busier and busier, it’s generally busier at at the start of the week than the end and (with a bit of knowledge) activity is grouped towards the start of each month.
Here’s how it’s done (Suprisingly simply actually):
We’re merely looking at a Pivot Chart based on two fields; a date (‘SessionDate’) and Session_ID (this could as easily be Order_No, Call_ID etc). To create the 3 dimensions we simply create a Calculated Dimension each for; year(SessionDate), week(SessionDate) and weekday(SessionDate), for the expression all we need is 1 with the contents =” and why not give it the label =” while we’re at it; that forms the basics of the chart.
Getting the colour blocks:
First choose your colour; in this case I’m using red which has an rgb value of 255,0,0. Now we have to create a process to correctly create the colour graduation (this is my favourite bit); to do this we add a new Variable (v_Colour_Index) and give it the expression:
This will give us a value that is the same as; 255 divided by the maximum number of Server Sessions on a single day within our selection set; I call it the ‘Indexation Value’, lets say our max number of Sesion_ID’s in one day is 75, therefore our result is 3.4. Why do we need this value? Well if we take each individual days Session count and multiply it by the ‘Indexation Value’ we’ll end up with a value between zero and 255 and when we useit in an argb() function it can control the graduation. So to deploy simply add the following to the charts expression ‘Background Color’:
So what does this do; it basically makes the graduations of each block relative to the maximum – which will be 100% ie; bright red, if we make selections and the max changes so does the chart – we could of course make this always relative to the max activity across the whole dataset regardless of selection.
As a final tidy up; check the ‘Always Fully Expanded’ checkbox and drag the Day dimension to be horizontal, re-size the columns accordingly and set the left/right column settings.
You could of course add values for each block to get specifics across but I like the chart as it is; it’s simple but effective, it is of course fully re-active to selections. I only knocked this together today so may well make tweaks down the line but if you have any suggestions just let me know.
Quick note: I’ve not included ‘Month’ as a dimension as it forces gaps into the chart when a week spans the end of one month and the start of the next.
Hope you can put it to use.