Color cell background according to field selection

[#1]

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.

[Anonymous]

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.

[#3 · Accepted solution]

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