topic Re: How to color above/below expression on line chart in QlikView

How to color above/below expression on line chart

Tue, 29 Apr 2014 17:12:24 GMT

Hello all,

I am currently trying to convert a few charts I have in Excel to QlikView. Essentially, this chart needs to be separated into two colors (Red and Green. I will input a "Plan" expression using a table containing months and values. For example, Jan= 5, Feb =4, etc. I need the chart to have a background color of red for all values above the plan line and green for any values on or under the plan line. I also need to be able to see the "Actual" line (generated from another table of months and values) so I don't think using the background color tab will help much. Here is a picture of the Excel chart I am trying to recreate for your reference.

I attempted to create this chart in QlikView by using the 4th style down within the line chart style tab and using the two expressions: (=Value) and (=10-Value). This looks similar, but will not allow me to color the line a different color than the background (I need the bar to be blue and anything below it to be green.

Please let me know if you have any ideas. Thanks in advance.

Pete

Re: How to color above/below expression on line chart

Gysbert_Wassenaar — Tue, 29 Apr 2014 17:45:06 GMT

I think you'll have to use two charts on top of each other. See attached qvw.

Re: How to color above/below expression on line chart

swuehl — Tue, 29 Apr 2014 17:45:48 GMT

Maybe use a combo chart, with a stacked bar chart with cluster distance set to zero and an additional line expression.

Something like attached.

Re: How to color above/below expression on line chart

Wed, 30 Apr 2014 18:06:11 GMT

This looks like it will be my best bet and will fit my needs. However, can you please explain what exactly the background color expression accomplishes? (if(sum(actual) and below(sum(actual)) ,color(1),red(0)))' Also, why did you include the (+sum({1}0) ) to the end of the "Actual" expression. I am unfamiliar with these parameters. Thanks a lot!

Re: How to color above/below expression on line chart

Wed, 30 Apr 2014 18:22:59 GMT

Also, by using this expression for background color, I lose the last symbols when I turn symbols on. Is there anyway around this?

Re: Re: How to color above/below expression on line chart

Gysbert_Wassenaar — Wed, 30 Apr 2014 19:21:29 GMT

I did that because I used only one expression in the top chart. But as I considered your question I realized the result can be achieved more easily by adding the expression for sum(plan) too and make sure that the background color for that expression is set to 100% transparent. See attached qvw.

Re: How to color above/below expression on line chart

Thu, 01 May 2014 14:40:11 GMT

Much better, thanks again!

Re: How to color above/below expression on line chart

Mon, 04 May 2015 10:14:00 GMT

I like this idea but not sure how to apply it to my situation, I do not have a 10 that I can do the calculation with. I just have a target value, but this can change from millions to billions. I need to get the color above the value to a light red and the colorr below the line to a light green. I do get the transparency etc