Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
JV
I'm guessing it will be something like
IF([Growth]/sum(TOTAL Growth) > 0.8 ,RGB(0,160,0),..
Regards,
M.
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.
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
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
Growth is dimension or Expression ?
Please post some sample qvw and the expression depends on the ur data model as well.
Growth is an integer that is created from this expression:
Sum({<[4_WEEK_ROLLING_RETAIL]={"P4WTY"}>} TRX_QTY_NORM))
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.