Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Background Color

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))))))))

Growth.JPG.jpg

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.

JV

8 Replies
martynlloyd
Partner - Creator III
Partner - Creator III

I'm guessing it will be something like

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

Regards,

M.

Not applicable
Author

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.

Not applicable
Author

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

Not applicable
Author

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.

JV

Not applicable
Author

Growth is dimension or Expression ?

Not applicable
Author

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

Not applicable
Author

Growth is an integer that is created from this expression:

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

Growth Expression.JPG.jpg

Not applicable
Author

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.