enabling background color in pivot show all values for dimension.

[#1]

Hello,

I have 4 dimensions and 2 expressions in my pivot table.

my expression is using set-analysis to restrict data.

SUM ({$<[CalendarDate] = {">=$(=date(Today(0) - 32,'YYYY-MM-DD'))"}, [RATING SUPER GROUP NAME] = {"Voice"}>}[IBI TRAF DUR]/60)

The pivot shows fine without any color coding.

but if i enable background color for either dimension or expression,

all the values of dimension and all values of the expressions appear in the pivot.

is there any way to avoid this?

should'nt color be applicable on the values of cell after the table is formed based on the expression with set analysis

Visual cues wont work for me, as i have to color code the dimension as well ( that do not match a valid configuration.)

[kaushiknsolanki]

Hi,

     In the color expression, you should use the same set expression which you have used in the expression, to have the same effect on chart.

Regards,

Kaushik Solanki

[#3]

The expression is as shown in the post.

the color coding is a simple if condition.

sorry for my naive question, but could you let me know how to have the set analysis in the If condition ?

[kaushiknsolanki]

Hi,

     What if statement you have written?

Regards,

Kaushik Solanki

[#5]

if( VLD_CF <> 1, LightRed(), Green())

the VLD_CF is a field in the table.

For rows that are valid, have it as 1, other have it as NULL as this field is a result of a join of the main table to another config table where we only have valid values.

basically the table has a list of fields, each field may have n number of values,

based on the config table the valid values in the fields are identified.

now without the color coding when i plot the pivot table, all the values ( the field that i am summing up -  that have NULL) in them are hidden, and the field values with some value are shown ,

what i am trying to do is highlight the values that have some values that can be summed up but are not valid as per configuration.

Example. :

Master table:

F1 , F2_code, VLD_CF,DUR

A,1,1,10

A,2,-,10

B,1,-,10

B,2,1,10

Dimension tables:

F2_Code, F2 name

1,X

2,Y

3,Z

VLD_CF  with "-" means it is a null value.

so basically i am trying to highlight  the SUM(DUR) for the row A,Y,  & B,X

but when i enable the background color,

the Z values also start to appear in the main table.

[kaushiknsolanki · Accepted solution]

Hi,

     Try this expression.

if(only({$<[CalendarDate] = {">=$(=date(Today(0) - 32,'YYYY-MM-DD'))"}, [RATING SUPER GROUP NAME] = {"Voice"}>}VLD_CF)=1,

LightRed(),Green())

Regards,

Kaushik Solanki

[#7]

Thank you,

that worked.