Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
mayankraoka
Specialist
Specialist

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

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 !

1 Solution

Accepted Solutions
johnw
MVP
MVP

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

View solution in original post

13 Replies
johnw
MVP
MVP

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
Specialist
Specialist
Author

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
MVP
MVP

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

View solution in original post

mayankraoka
Specialist
Specialist
Author

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
johnw
MVP
MVP

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
MVP
MVP

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
Specialist
Specialist
Author

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
MVP
MVP

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.