Background Color Expression with If Statement

[strenger11]

I could use some help with a color expression that is more complex than I am used to.  The OverrideSummary photo provided shows what I am trying to accomplish in QlikSense.  For the current week, if the override % is less than the average override % for the last 4 weeks, then I want it to have a red background color, but if it's not less than the average override % for the last 4 weeks, but is less than the previous week, I want it to have a yellow background color.  The override % is calculated as follows:

Count({<OverrideIndicator={'Y'},ProductLine={"Paints"}>}OverrideIndicator)
/
Count(ProductLine={"Paints"}>}OverridePriceInd)

I prefer to only have the background color on the current week, but if it needs to be on all the weeks, that is fine too.

Thank you in advance.

[lfetensini · Accepted solution]

Try something like that:

If(
 RangeAvg(Before([YOURFORMULA], 1, 4)) < [YOURFORMULA],
 Red(),
 
 If(
  Before([YOURFORMULA]) < [YOURFORMULA],
  Yellow()
 )
 
)

[strenger11]

That worked!  Thank you Lftensini.  I now get something that looks like this: