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

kwarren1 — Mon, 20 Mar 2017 21:08:18 GMT

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.

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

marcus_sommer — Tue, 21 Mar 2017 09:38:39 GMT

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

- Marcus

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

swuehl — Tue, 21 Mar 2017 09:47:32 GMT

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

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

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

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

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