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:
 
=255/max(aggr(count(Session_ID),SessionDate)
 
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’:
 
=argb(count(Session_ID)*v_Colour_Index,255,0,0)
 
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.
 
Matt
Advertisements
Comments
8 Responses to “Qlikview Activity Ribbon Chart”
  1. mellerbeck says:

    Awesome! I think your third formula needs to be weekDay(SessionDate) instead of DayName

  2. Emn says:

    hello, i find this type of chart clever and useful to find some patterns.

    Tried to set i tup into my qlikview documents but it seems there is a problem with the place i put my argb function.

    I try to explain (my qlikview is in French, not sure the english terms will be the same) : -> pivot table properties screen -> tab : ‘visual indicators’, -> normal informations -> background -> calculated and i put the argb function like described. but all my blocks are 255 coloured value.

    What did i forget ? Has ayone an idea ?
    That gets on my nerve 😦

    • Emn says:

      Finally i found the answer. Some of the Qlikview controls are not very intuitive. I have to use the ‘+’ nearby the expression to set up the coulour function and not the “visual indicator background”.

  3. Hi – i have used this to highlight overtime costs by week. excellent – and easy to use – method. One question i had from users. When they export to excel you lose the gradient of the colours. Is there any clever way of giving users a way to download this chart to distribute – apart from printing to pdf or taking a screen grab. keep up the good work.

    • Tim says:

      I am not sure if you solved this Brian, but instead of using the alpha channel in argb() you need to actually change the colour itself. You can do this using the Colormix Wizard [sic] in the expression editor and then choosing the upper bound colour as your “hot” colour and then white as your lower bound colour. This will then give you a transition between white and the hot colour which looks almost identical to using the alpha channel.

      I had to use this method to ensure that the colours appeared correctly in web view as argb() appears to ignore the aplha channel completely and makes the colour fully opaque.

Trackbacks
Check out what others are saying...


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: