topic Re: Between function in QlikView

Between function

emilyapril — Sat, 16 Nov 2024 04:28:52 GMT

Hi!

I have a table which uses the following expression:

=sum([Last Month Forecast]-[Current Forecast])

This will give me a negative number or positive number.
Id like to format the background of each cell depending on the number.
I cannot use visual cues as I have THREE ranges Id like to work with. One of these ranges, being between 1 & 60.

I had worked out the expression for a basic, two part "if statement" but cannot find the right way to put "between" in the expression:

example- (which with a calculation in place of the underscores, is Ok. )

sum(
if([Last Month Forecast]-[Current Forecast]<=0,RGB(255,0,0),
if([Last Month Forecast]-[Current Forecast]>=61,RGB(0,255,0),
if([Last Month Forecast]-[Current Forecast]_________,RGB(0,0,255)
))))

so where i have the ___ I need to say between 1 & 60..

many thanks in advance!

Re: Between function

MindaugasBacius — Tue, 12 Feb 2019 10:53:31 GMT

You just do not need the last If statement 🙂

if([Last Month Forecast]-[Current Forecast]<=0,RGB(255,0,0),
if([Last Month Forecast]-[Current Forecast]>=61,RGB(0,255,0),RGB(0,0,255)))

Add this expression to:

This should help you solve the issue.

Re: Between function

lockematthewp — Wed, 06 Mar 2019 16:59:17 GMT

sum(
if([Last Month Forecast]-[Current Forecast]<=0,RGB(255,0,0),
if([Last Month Forecast]-[Current Forecast]>=61,RGB(0,255,0),
if(([Last Month Forecast]-[Current Forecast]> 0) and ([Last Month Forecast]-[Current Forecast] < 61),RGB(0,0,255)
))))