topic Re: Background color for Pivot Table cells when cell value exceeds aggregate value in QlikView

Background color for Pivot Table cells when cell value exceeds aggregate value

bvikramreddy89 — Sun, 20 Sep 2020 13:15:18 GMT

Hi,

Am trying a possibility for setting background color for pivot table cells when cell values greater than "avg + Std" and less than "avg - std" of individual days of month over that year i.e.

Cells of Jan 1st, Feb 1st ... Dec 1st shoud be compared with "Avg + Std and Avg-Std" of these 12 values. Similarly,
2nd of every month and 3rd of every month, so on till 31st for that year.

I have Days(Vertical) of month from 1 to 31 as one dimension and year-month(Horizontal) as other dimension. Have filled data cells.

Am using below but its not working:

if(value >vAvdStdPlus or value<vAvgStdMinus, Lightred(), Green())

Where:
vAvdStdPlus = aggr(avg(value),Month_day, Year) + aggr(StDev(value),Month_day, Year)
vAvgStdMinus = aggr(avg(value),Month_day, Year) - aggr(StDev(value),Month_day, Year)

Am attching, table format in excel . Please help me with this.

Re: Background color for Pivot Table cells when cell value exceeds aggregate value

Kushal_Chawda — Sun, 20 Sep 2020 14:52:07 GMT

@bvikramreddy89 try below expression

//change variable expressions as below (include "=" ) vAvdStdPlus = sum(aggr(avg(value),Month_day, Year)) + sum(aggr(StDev(value),Month_day, Year)) vAvgStdMinus = sum(aggr(avg(value),Month_day, Year)) - sum(aggr(StDev(value),Month_day, Year)) // Now you can try below expression in background color expression if(sum(value) > $(vAvdStdPlus) or sum(value)<$(vAvgStdMinus), Lightred(), Green())

Re: Background color for Pivot Table cells when cell value exceeds aggregate value

bvikramreddy89 — Tue, 22 Sep 2020 12:14:31 GMT

Hi Kush,
Thank you for the reply but i already have tried that and its not worked.
The expression is not applying to all the cells but only to few. Thats the main issue.

Does anyone worked out similar requirement. Can anyone suggest any other possibility.

Re: Background color for Pivot Table cells when cell value exceeds aggregate value

sunny_talwar — Tue, 22 Sep 2020 12:24:52 GMT

How about you try this

Avg(TOTAL <Month_day> value) + StDev(TOTAL <Month_day> value) Avg(TOTAL <Month_day> value) - StDev(TOTAL <Month_day> value)

Re: Background color for Pivot Table cells when cell value exceeds aggregate value

bvikramreddy89 — Fri, 25 Sep 2020 13:18:43 GMT

Hi Sunny,

Thank you very much. The expression worked.