topic Re: Conditional formatting in Pivot table not working propoerly in QlikView

Conditional formatting in Pivot table not working propoerly

b_garside — Fri, 20 Dec 2013 18:37:11 GMT

I have a Pivot table that displays bandwidth as a percentage 0-1. I have bands of colors 5 of them based on .2 intervals.

For whatever reason my formula is not treating the colors equally. If I use a single column straight table it works fine.

In some cases a value done not get any color.

Unfortunately I can not upload a QVW on this one since its a customer.

Bandwitdht ranges usually in increments of 1.6 but once summed up you get larger values but the max rang is 95 on any given hour.

vDayScale = 30.42

MaxBandWidthRange = 95

eBandWidth = (Sum(BandWidth) / vDayScale) / MaxBandWidthRange

If( $(eBandWidth) >= 1 , RGB(0,110,255) ,
If( $(eBandWidth) >= .8 and $(eBandWidth) <= .99 , RGB(40,140,255) ,
If( $(eBandWidth) >= .60 and $(eBandWidth) <= .79 , RGB(75,155,255) ,
If( $(eBandWidth) >= .40 and $(eBandWidth) <= .59 , RGB(110,175,255) ,
If( $(eBandWidth) >= .20 and $(eBandWidth) <= .39 , RGB(145,195,255) ,
If( $(eBandWidth) >= .01 and $(eBandWidth) <= .19 , RGB(221,240,255)
) ) ) ) ) )

Re: Conditional formatting in Pivot table not working propoerly

swuehl — Fri, 20 Dec 2013 18:43:40 GMT

Could it be a rounding issue?

I mean, if sum(BandWidth) is for example .194 or .196, it may be shown as 0.19 resp. 0.20, but your background color expression seems not to cover these values.

Re: Conditional formatting in Pivot table not working propoerly

b_garside — Fri, 20 Dec 2013 18:48:21 GMT

I was starting to think the same and still may be one of the issues.

But when I used the ColorMix wizard It would only evaluate a Single column at a time so you would have dark colors with high value next a low.

Re: Conditional formatting in Pivot table not working propoerly

b_garside — Fri, 20 Dec 2013 18:53:35 GMT

How would i round it ? use Round function or Ceil, Floor.

Re: Conditional formatting in Pivot table not working propoerly

swuehl — Fri, 20 Dec 2013 18:53:44 GMT

No, I was thinking of something like

If( $(eBandWidth) >= 1 , RGB(0,110,255) ,
If( $(eBandWidth) >= .8 and $(eBandWidth) < 1 , RGB(40,140,255) ,
If( $(eBandWidth) >= .60 and $(eBandWidth) < .8 , RGB(75,155,255) ,
If( $(eBandWidth) >= .40 and $(eBandWidth) < .6 , RGB(110,175,255) ,
If( $(eBandWidth) >= .20 and $(eBandWidth) < .4 , RGB(145,195,255) ,
If( $(eBandWidth) >= .01 and $(eBandWidth) < .2 , RGB(221,240,255)
) ) ) ) ) )

Re: Conditional formatting in Pivot table not working propoerly

b_garside — Fri, 20 Dec 2013 19:01:37 GMT

Still left some uncolored while smaller values disappeared since its rounding up.

Round((Sum(BandWidth / vDayScale)) / MaxBandWidth, 0.01 )

Re: Conditional formatting in Pivot table not working propoerly

b_garside — Fri, 20 Dec 2013 19:14:51 GMT

Ok yeah. That really helped. Makes sense. Don't leave any small gaps.

Re: Conditional formatting in Pivot table not working propoerly

b_garside — Fri, 20 Dec 2013 19:42:32 GMT

So it seemed too good to be true. See below

Percent values...correlate no colors in some cells.

Re: Conditional formatting in Pivot table not working propoerly

swuehl — Fri, 20 Dec 2013 19:47:12 GMT

Where do the large values suddenly come from? Have you removed the division by your constants?

I thought it is intentional, but your lower limit for a color is still >= 0.01?

Re: Conditional formatting in Pivot table not working propoerly

b_garside — Fri, 20 Dec 2013 19:47:36 GMT

It must be since the values are too low? I'll let it be.

Re: Conditional formatting in Pivot table not working propoerly

swuehl — Fri, 20 Dec 2013 19:48:57 GMT

If( $(eBandWidth) >= 1 , RGB(0,110,255) ,
If( $(eBandWidth) >= .8 and $(eBandWidth) < 1 , RGB(40,140,255) ,
If( $(eBandWidth) >= .60 and $(eBandWidth) < .8 , RGB(75,155,255) ,
If( $(eBandWidth) >= .40 and $(eBandWidth) < .6 , RGB(110,175,255) ,
If( $(eBandWidth) >= .20 and $(eBandWidth) < .4 , RGB(145,195,255) ,
If( $(eBandWidth) >= .0 and $(eBandWidth) < .2 , RGB(221,240,255)
) ) ) ) ) )

Re: Conditional formatting in Pivot table not working propoerly

b_garside — Fri, 20 Dec 2013 19:50:24 GMT

Yeah I kept the % expression in place for the coloring.

But changed the main one to sum(BandWidth) to see the raw values accumaulated

Re: Conditional formatting in Pivot table not working propoerly

b_garside — Fri, 20 Dec 2013 19:52:23 GMT

That did the trick. Duh... simple tweak. Thanks!