Convert Nested If's to Div: Pivot Table Acting As Heatmap Background Color

[kwarren1]

Hello,

The following background color equation for my pivot table works great in the qlikview app, but when posted on the server it flashes and the pivot table object is blank white. I think I am using too many nested if statements for it to work when moved to the server.

Can anyone help me convert the below equation using the Div Function?

NESTED IFS

If(IsNull(Avg(DEMAND)), RGB(0, 0, 0),

       IF(div((Avg(DEMAND)-MIN(total <Month> DEMAND)), ((MAX(total <Month> DEMAND) - MIN(total <Month> DEMAND))/12)) = 0 ,        RGB(245, 255, 240),

  If($(vScale0) <= Avg(DEMAND) and Avg(DEMAND) < $(vScale1), RGB(245, 255, 240),

  If($(vScale1) <= Avg(DEMAND) and Avg(DEMAND) < $(vScale2), RGB(193, 255, 193),

  If($(vScale2) <= Avg(DEMAND) and Avg(DEMAND) < $(vScale3), RGB(180, 238, 180),

  If($(vScale3) <= Avg(DEMAND) and Avg(DEMAND) < $(vScale4), RGB(0, 250, 154),

  If($(vScale4) <= Avg(DEMAND) and Avg(DEMAND) < $(vScale5), RGB(179, 238, 58),

  If($(vScale5) <= Avg(DEMAND) and Avg(DEMAND) < $(vScale6), RGB(192, 255, 62),

  If($(vScale6) <= Avg(DEMAND) and Avg(DEMAND) < $(vScale7), RGB(255, 255, 0),

  If($(vScale7) <= Avg(DEMAND) and Avg(DEMAND) < $(vScale8), RGB(255, 204, 0),

  If($(vScale8) <= Avg(DEMAND) and Avg(DEMAND) < $(vScale9), RGB(255, 170, 0),

  If($(vScale9) <= Avg(DEMAND) and Avg(DEMAND) < $(vScale10), RGB(255, 136, 0),

  If($(vScale10) <= Avg(DEMAND) and Avg(DEMAND) < $(vScale11), RGB(255, 85, 0),

  If($(vScale11) <= Avg(DEMAND) and Avg(DEMAND) < $(vScale12), RGB(255, 51, 0),

  If($(vScale12) <= Avg(DEMAND), RGB(255, 0, 0), RGB(0, 0, 0))

   ) ) ))  ) ) )  ) ) ) )  ) ) )

First Attempt at Converting to DIV

IF(IsNull(Avg(DEMAND)),black(),

  IF(div((Avg(DEMAND)-MIN(DEMAND)), ((MAX(DEMAND) - MIN(DEMAND))/12)) = 0 ,

RGB(0,0, 0), pick(div((Avg(DEMAND)-MIN(DEMAND)), ((MAX(DEMAND) - MIN(DEMAND))/12)),

RGB(245, 255, 240), RGB(193, 255, 193), RGB(180, 238, 180), RGB(0, 250, 154), RGB(179, 238, 58), RGB(192, 255, 62), RGB(255, 255, 0), RGB(255, 204, 0), RGB(255, 170, 0), RGB(255, 136, 0), RGB(255, 85, 0), RGB(255, 51, 0), RGB(255, 0, 0))))

It does not return all 12 colors. Since I am doing a min and max, I am expecting to get atleast the min and max color represented on pivot table background color and it currently is not.

[marcus_sommer]

Which values contain your variables $(vScale0) - $(vScale12) ?

- Marcus

[swuehl]

It's hard to tell without knowing the context of these expressions.

Note that Avg(DEMAND), Min(DEMAND) and Max(DEMAND) may return the same value, depending on the context of the aggregations (that could explain the difference to your first expression where you were using a TOTAL qualifier in some aggregations).

Debugging kind of these issues is much more easy with a sample provided.

Finally, have you considered using one of the color functions available in Qlik, like ColorMix1()?