Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
) ) ) ) ) )
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)
) ) ) ) ) )
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.
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.
How would i round it ? use Round function or Ceil, Floor.
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)
) ) ) ) ) )
Still left some uncolored while smaller values disappeared since its rounding up.
Round((Sum(BandWidth / vDayScale)) / MaxBandWidth, 0.01 )
Ok yeah. That really helped. Makes sense. Don't leave any small gaps.
So it seemed too good to be true. See below
Percent values...correlate no colors in some cells.
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?
It must be since the values are too low? I'll let it be.