Thursday 19 July 2012

Lose the Legend in Line Charts

I read an interesting post this morning by Gregor Aisch entitled Doing the Line Charts Right.

One of the features that he mentions is about labeling the lines rather than having a legend.  This is not a feature that we have natively in QlikView, but it is achievable.


The "default" option in QlikView is to have a legend in the line chart.  The problem being that the user has to look to the legend then look back to the chart - which can cause difficultly, especially as the number of lines increases.

To change this, we can make use of the Value on Data Point option and using Dual in the expression.

Here is the expression that I am going to use:


 Dual(
 If(MonthYear=Max(total MonthYear), CategoryType, ''),
 Sum(Sales)
 )


Then I enable the Value on Data Point option (which you wouldn't normally apply to line charts).  The value that will be displayed is the Text in the dual, not the number (also make sure that the number format is "Expression Default") which will be blank except for the last point.  Then we end up with this:


Just what I was looking for.


Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner
Follow me on Twitter: @stephencredmond

14 comments:

  1. Wow!
    How clever!

    Alexis
    KiteSoft Limited
    Cyprus

    ReplyDelete
  2. Hi Stephen,

    Cool! If often use a similar trick* to show the numerical value on the last data point, but using the dual function to display the label makes it even nicer.

    Cheers,
    Barry

    * I use this expression, which you can drop into any line chart without modification: RowNo() = NoOfRows()

    ReplyDelete
  3. Hi Stephen,

    This is a great idea. Is this version specific? I am trying to get this working in QV 10 and had no luck with it. The issue is I can only get the numeric value but not the labels and the numeric value (in my case) needs to be of "Money" type.

    Thanks,
    Suda

    ReplyDelete
  4. Turn the Number format to "Expression Default". Use the Num function to format the result.

    ReplyDelete
  5. Stephen, any idea why this might not work? this is my formula and it will show me the data correctly on the Axis but not on the data point

    Dual(
    if (Date = Max(total {$< Fact = {'V'} >} Date), StreetName, ' ')
    ,
    sum({$< Fact = {'V'} >} Count)
    )

    ReplyDelete
  6. I can't say without your data but do the separate elements of the dual, especially the text, work as different expressions if you convert the chart to a straight table?

    ReplyDelete
  7. I like the idea of using the Dual function to show the label on the chart instead of in the legend, but one drawback is that it removes the ability to use the pop-up label functionality. Have you found a way around this?

    ReplyDelete
  8. Rebecca, have a look at this other post:

    http://qliktips.blogspot.ie/2009/09/custom-popup-labels-on-bar-chart.html

    ReplyDelete
  9. It won't works when I have Full Accumulation.. Any idea how to solve it?

    ReplyDelete
  10. Great post! However, I would like that the axis are in % and it does not work if I select "Expression default" for the number format. Is it possible to combine the dual expression and the axis in %?

    ReplyDelete
  11. Hi there, I'm wondering if this could be applied to a pie chart?
    Thanks :-)

    ReplyDelete
  12. Hi

    This doesn't seem to be working if using both primary and secondary axes. It shows only one of the labels and on the wrong position too.

    ReplyDelete
  13. Great tip.. The text on the lines will be difficult to read when the lines/dot come close to each other.
    Robinson

    ReplyDelete

Note: only a member of this blog may post a comment.