Facing issue with filling same rgb(colour) to last week

[mayankraoka]

Hi All,

I am having an requirement ,In which I have 3 dimension customer name,seg and week.Week is pivoted .I have attached the sample application for example.

In the attached example,Refer row 5 in this example I want the same RGB of 20151211 value 0.1 and after that all blank value should be same color as of value 0.1% and earlier blank value of week-20151204 should be grey.In short  whenever there is value after that if there is blank value that should have same rgb as that of earlier value column.If first value is blank that it should be grey color.

Can anyone help me or let me know in case of any other info required?

Regards,

Mayank !

[johnw]

I'm not aware of a way to conditionally color cells with null in them.

So maybe use custom format cell to add the gray for the null cells, and then write an expression that puts blanks in the cells after the value, and write some appropriate background color expression. This worked:

if(Value,Value,if(rangesum(before(Value,1,99)),' '))

And since I don't know what colors you want, just set it to a light green:

rgb(180,230,180)

[mayankraoka]

Hi John,

Thank you very much for replying so quickly.The other thing, I am trying to assign different colour for value range 0-5% red,5-10% green and so on..Any any value after this is blank then it follow the previous value colour.Fo example if value is 4% and its next 2 columns are blank all 3 columns should be red colour.The next column is 6% so it should be green colour and if there any value is blank then it should also be green colour.

Can you help me on this please?

Regards,

Mayank

[johnw · Accepted solution]

There's hopefully a way to use a valueloop() to do this, but I wasn't able to figure it out quickly, so here's a brute force color expression. Add additional colors to the end of the list for increasingly higher values. Add as many Values as you might ever need - which is where this gets very impractical.

pick(ceil(200*

alt(before(Value,0),before(Value,1),before(Value,2)

  ,before(Value,3),before(Value,4),before(Value,5)

  ,before(Value,6),before(Value,7),before(Value,8)

  ,before(Value,9),before(Value,10),before(Value,11)

  ,before(Value,12),before(Value,13),before(Value,14)))

,rgb(250,200,200),rgb(200,250,200))

[mayankraoka]

Hi John,

Thank you so much .It is exactly I want,But its bit difficult for me to understand the expression for background colour.

Can you please explain me the below expression for my understanding:

pick(ceil(200*

alt(before(Value,0),before(Value,1),before(Value,2)

  ,before(Value,3),before(Value,4),before(Value,5)

  ,before(Value,6),before(Value,7),before(Value,8)

  ,before(Value,9),before(Value,10),before(Value,11)

  ,before(Value,12),before(Value,13),before(Value,14)))

,rgb(250,200,200),rgb(200,250,200))

Regards,

Mayank

[Anil_Babu_Samineni]

Nayank,

Try this

=if(fieldname='value',RGB(255,0,0))

// =If(Only(Value) = '0.1%', Value,RGB(255,0,0)) {In that, You can try Where YYMM='Value'}

Note: Please mention this expression on your Expression Background and then try

[johnw]

pick(ceil(200*
alt(before(Value,0),before(Value,1),before(Value,2)
  ,before(Value,3),before(Value,4),before(Value,5)
  ,before(Value,6),before(Value,7),before(Value,8)
  ,before(Value,9),before(Value,10),before(Value,11)
  ,before(Value,12),before(Value,13),before(Value,14)))
,rgb(250,200,200),rgb(200,250,200))

Working mostly from inside out...

Before(Value,3) says "give me the value of field Value in the column 3 to the left".

Alt(...) says "give me the first item in this list that isn't null".

So then alt(before(Value,0),before(Value,1),...) says "give me the first non-null value that you find starting with this column and working your way left". If you used that much of it as your chart expression, you'd see that it takes each value and replicates it to the right until running into another value, or the end of the chart, or since I only went to 14, to 14 places over, at which point it stops.

200*alt(...) will take a 0.5130%, for instance, and turn it into 1.026.

Ceil(...) says to round up to the next integer, so 2 in this case.

So ceil(200*...) returns 0 for 0%, 1 for >0% to 0.5%, 2 for >0.5% to 1%, 3 for >1% to 1.5%, and so on.

Pick(N,A,B,C,D) says "if N = 1 use A, if N = 2 use B, if N = 3 use C, if N = 4 use D, else return null".

Rgb(...) is of course a color expression.

So the pick will return a light red for >0% to 0.5%, and a light green for >0.5% to 1%. And since that percent will be from whichever value it finds first to the left, then the color will extend from whatever cell has the value and over into the cells to the right.

What I hate about my expression is the need to explicitly list all the values 0-14 or however high you might need to go. I want to be able to write something like this:

pick(ceil(200*alt(before(Value,valueloop(0,999)))),rgb(250,200,200),rgb(200,250,200))

But that's unfortunately not valid. I bet there's some valid syntax for doing it, but I didn't find it with a little poking.

I'm looking at a possible data model solution right now.

[johnw]

Eh, neither of my two data model ideas panned out quickly enough, and I probably shouldn't spend more time on it. The two ideas were using an AsOf table to link the weeks to previous weeks, or filling in all the missing cells including a ColorValue (which clones the value to future weeks) and Color (set from the ColorValue).

[mayankraoka]

Hi John,

Thank you very much for your detail explanation and time.Really appreciate your help.

It really helps me to understand each and every function clearly.

Thank you once again.

Regards,

Mayank

[johnw]

I figured out how to do a valueloop. This should work as your color expression.

pick(ceil(200*alt($(=concat('before(Value,'&valueloop(0,99)&')',',')))),rgb(250,200,200),rgb(200,250,200))

And I suppose I should explain the changed part. I've replaced this:

before(Value,0),before(Value,1),before(Value,2)
,before(Value,3),before(Value,4),before(Value,5)
,before(Value,6),before(Value,7),before(Value,8)
,before(Value,9),before(Value,10),before(Value,11)
,before(Value,12),before(Value,13),before(Value,14)

With this:

$(=concat('before(Value,'&valueloop(0,99)&')',','))

Valueloop(0,99) generates a list of integers between 0 and 99.

I'm also adding some text literals around the integer, such that the first value in the list I'm generating will be:

before(Value,0)

And the list will contain that all the way from 0 to 99.

The concat(...,',') will concatenate them all together with commas in between.

$(=...) is dollar-sign expansion, which will insert that concatenated list into the expression before evaluating the rest of the expression. So the final expression that's being evaluated is just like the original, except that it can go 99 cells to the left/right instead of 14. And you can plug whatever number you want in there if 99 won't be enough. You'll just have to change the real expression too, since that maxes out at 99 as well.