topic Re: Color cell background according to field selection in QlikView

Color cell background according to field selection

Mon, 14 Nov 2011 09:55:19 GMT

Hi QVers,

I tried to find an answer in other discussions but nothing applied in my case.

I have a field 'Date', and a Pivot Table used as a calendar (expression = 'Date'). But now I would like to change the background color according to the selection in the field, i.e. :

if 'Date' in FieldSelection('Date'), green,

else, white

I can't find a way to do it without set analysis and since I ahev no aggregation, can't use it...

If someone has a good idea, thanks in advance.

Re: Color cell background according to field selection

Anonymous — Mon, 14 Nov 2011 10:06:09 GMT

You can add this color expression to you expression by clicking on the little plus infort of your expression:

Or you could use the color settings on the 'Visual Cues' tab

Set Normal to green and Upper and Lower to white, and fill in the right field name. (this only works if you select 1 value)

Good Luck,

Dennis.

Color cell background according to field selection

Mon, 14 Nov 2011 10:31:40 GMT

Yes, I know where to set a background color expression, the problem is to find a correct expression.

And I want to be able to have many dates selected.

So as soon as some random dates are selected (through an other object), I want the selected dates background to turn green on the pivot table.

And the expression I wrote previously doesn't even exist. It was just to give an idea of what I want.

Color cell background according to field selection

Anonymous — Mon, 14 Nov 2011 11:58:41 GMT

I see.

How does your pivot looks like? Can you share it?

Re: Color cell background according to field selection

Mon, 14 Nov 2011 12:38:01 GMT

It really simple :

day of the week on lines and week number on columns. See attached.

Re: Color cell background according to field selection

SunilChauhan — Mon, 14 Nov 2011 12:40:15 GMT

click on + before expression

-> backgroundcolor->

if(GetFieldSelections(Date),Green(),white())

see the attached file

hope this help

Re: Color cell background according to field selection

Mon, 14 Nov 2011 13:04:51 GMT

I did try it, but hopelessly it doesn't work...

Maybe I'm missing something. Just to be sure, as shown on the picture in previous message, my pivot table is set as following:

dimension 1 : day of the week

dimension 2 : week number

expression : date

Re: Color cell background according to field selection

Anonymous — Mon, 14 Nov 2011 13:54:57 GMT

Take a look at the attached file.

Is this what you are looking for?

Re: Color cell background according to field selection

Mon, 14 Nov 2011 14:52:19 GMT

I can't open your file, no more recovery allowed...

Re: Color cell background according to field selection

Mon, 14 Nov 2011 15:40:03 GMT

Can you explain what you did without any Qlikview document? (I'm on personal edition, so I'm no more able to open others .qvw).

Thx

Re: Color cell background according to field selection

Anonymous — Tue, 15 Nov 2011 07:55:05 GMT

First I made a calender (Year, month, week, weekday, etc) bases on a date field.

Select 1 year in and rightt click the year field to check "Always One selected Value"

Then I used Weeknumber and WeekDay in a pivot table (like your example)

And used the following expression :

=day(max(datefield))

With the following background color forumla:

=IF (IsNull(WeekDay),white(),If(count(datefield)=1, Green()))

And the following for the text formula

=IF (IsNull(WeekDay),white(), If(count(datefield)=1, Green()))

Set the pivot table to "Show all Values" (Dimension tab) and uncheck "Supress zero values"(Presentation tab).

This way not only will the selection on your datefield show in your calender, it will also show any calender connected seletions (Week, Month, ect)

Let me know if this is what you are looking for, ok?

Good Luck,

Dennis.

Re: Color cell background according to field selection

Tue, 15 Nov 2011 10:01:48 GMT

Hi,

that's exactly what I wanted, and with a little modification, it works perfect.

I don't think the expression:

=day(max(datefield))

is useful. It doesn't make any sens for me and when I put it in a textbox it doesn't show any result (maybe I'm missing something). But anyway, it works fine without it.

Then if I want your process to function, I have to change the end of expression for background color and text color and add a superior sign:

=IF (IsNull(weekday),white(),If(count(Datefield)>=1, Green()))

Then it works well.

Many thanks,

benetche