topic Re: Background Color in QlikView

Background Color

Mon, 13 Oct 2014 17:26:19 GMT

Hi,

I am color-coding cells based on their value using Background Color, like this:

IF([Growth] > '30' ,RGB(0,160,0),

IF([Growth] > '20' ,RGB(0,225,0),

IF([Growth] > '5' ,RGB(150,255,150),

IF ([Growth] >'0' ,RGB(255,255,235),

IF([Growth] > '-10' ,RGB(255,175,175),

IF([Growth] > '-20' ,RGB(255,140,140),

IF([Growth] < '-20', RGB(255,80,80))))))))

Works nicely.

But the ranges change massively based on filters. These are local results, and the numbers are never more than 30, or less than -20.

But when i remove filters and pull in the national data, the top number is more than 5000, and the bottom can be less than -2500.

Does anyone have an idea how to replace the '30' '20' '5' '0' '-10' '-20' with a variable that will calculate the background colors based on the full range of values in the Growth dimension?

Thanks for your thoughts.

Re: Background Color

martynlloyd — Mon, 13 Oct 2014 17:34:52 GMT

I'm guessing it will be something like

IF([Growth]/sum(TOTAL Growth) > 0.8 ,RGB(0,160,0),..

Regards,

Re: Background Color

Mon, 13 Oct 2014 17:43:46 GMT

Hi JV, I got 2 ideas on your requirements.

Method 1: Complex IF cond depends on the Length of Growth value

IF( Len([Growth]) = 4, Write the BG cond with 5000 to -2500 range , Write the BG cond with 30 to -25 range )

This Method should work but it kills the calculation depends on the size of your data.

Method 2 : Hide/Show logic with your selections

Create the 2 charts. One chart with low range values and other chart high range values. Enable the Hide/Show logic depends on the user selections.

Re: Background Color

Mon, 13 Oct 2014 20:00:14 GMT

Hi Martyn,

Nice idea, but I think I need to calculate range or something instead of sum. Or have the sum do an absolute on the negative numbers. Have you ever tried this?

Thanks, JV

Re: Background Color

Mon, 13 Oct 2014 20:10:25 GMT

Hi Dathu,

I just showed two filter examples. In reality, there could be hundreds of filter variations, so I was hoping for a dynamic calculation that gave me the full range of values from the Growth dimension column and put the specific Growth dimension value in the correct bucket (top 20%, next 20%, third 20% fourth 20% and bottom 20%).

Another problem is that the Growth dimension is actually a number returned from an expression, not a hard dimension, so QV has trouble seeing it. (error: Bad field name(s): Growth

Any other thoughts would be appreciated.

Re: Background Color

Mon, 13 Oct 2014 21:19:43 GMT

Growth is dimension or Expression ?

Re: Background Color

Mon, 13 Oct 2014 21:20:22 GMT

Please post some sample qvw and the expression depends on the ur data model as well.

Re: Background Color

Mon, 13 Oct 2014 22:00:42 GMT

Growth is an integer that is created from this expression:

Sum({<[4_WEEK_ROLLING_RETAIL]={"P4WTY"}>} TRX_QTY_NORM))

Re: Background Color

Mon, 13 Oct 2014 22:08:13 GMT

So I tried this, replacing the second Growth with the actual expression:

IF ([Growth]/sum(TOTAL Sum({<[4_WEEK_ROLLING_RETAIL]={"P4WTY"}>} TRX_QTY_NORM)) > 0.8 ,RGB(0,160,0),

IF([Growth]/sum(TOTAL Sum({<[4_WEEK_ROLLING_RETAIL]={"P4WTY"}>} TRX_QTY_NORM))) > 0.6 ,RGB(0,225,0),

IF([Growth]/sum(TOTAL Sum({<[4_WEEK_ROLLING_RETAIL]={"P4WTY"}>} TRX_QTY_NORM))) > 0.4 ,RGB(150,255,150),

IF ([Growth]/sum(TOTAL Sum({<[4_WEEK_ROLLING_RETAIL]={"P4WTY"}>} TRX_QTY_NORM))) > 0.2 ,RGB(255,255,235),

IF([Growth]/sum(TOTAL Sum({<[4_WEEK_ROLLING_RETAIL]={"P4WTY"}>} TRX_QTY_NORM))) > -0.1 ,RGB(255,175,175),

IF([Growth]/sum(TOTAL Sum({<[4_WEEK_ROLLING_RETAIL]={"P4WTY"}>} TRX_QTY_NORM))) > -0.3 ,RGB(255,140,140),

IF([Growth]/sum(TOTAL Sum({<[4_WEEK_ROLLING_RETAIL]={"P4WTY"}>} TRX_QTY_NORM))) > -0.5, RGB(255,80,80))))))))

Now there is an error in expression.