background color for pivot table problem

[bdunphy]

Hi ,

I would like to set the background color for a cell in a pivot table based on the value of the cell above the existing one. however when i do this - all 'zero' values are displayed in the pivot table - even though i have them set to be suppressed.

When i remove the background color condition - the pivot table behaves as expected.

Is there any way around this?

I think the problem is with the above() part.

Calc:

if

(YearWeek>=Version,

sum({$<Year={$(=max(Year)) },[Forecast Available]={'Yes'}>}Step_Forecast_Packs)
)

Condtion:

if

(Step_Forecast_Packs=above(Step_Forecast_Packs),rgb(200,200,200))

[swuehl]

You are right, I believe the problematic term is the above() function.

A Note in the Help pages for the chart inter record functions says that suppress zero value option is automatically disabled when using above() et.al.

What seems to work is to enclose the chart inter record functions in an advanced aggregation aggr() function,

like

=aggr( if(Step_Forecast_Packs=above(Step_Forecast_Packs),rgb(200,200,200)), YourPivotDimension)

[bdunphy]

Hi thanks for the reply. I cant get it to work however. I cant seem to upload an image here at the moment but my dimensions are: Customer name, Item name, Alias number, Version --> YearWeek (with YearWeek dragged up on top of the expression)

I have tried every order of the dimensions and cant get it to behave as i want. 

=

aggr( if(Step_Forecast_Packs<>above(Step_Forecast_Packs),rgb(200,200,200)), [Customer Name],[Alias number],[Item name],Version, YearWeek)

[swuehl · Accepted solution]

I assume that is due to the one dimension dragged to the top. Could you check by dragging it back to the vertical at the left?

Without testing, have you tried this order?

aggr( if(Step_Forecast_Packs<>above(Step_Forecast_Packs),rgb(200,200,200)),YearWeek, [Customer Name],[Alias number],[Item name],Version)

[#5]

Hi,

       All you have to do is first go to Settings-> User preference -> Design -> Check "Always Show design menu item"

Click ok.

Now right click on your chart cell and you will find an option "Custome format cell"

From here you can change the text and background color"

Thanks

Rathish

[bdunphy]

@rathishkumar - this works fine to highlight all expressions or dimensions but i need to conditionally highlight.

@swuehl Great thanks. just added one more 'and' to the IF statement to not highlight differences if the above product is a different product. ordering of the dimensions was the problem.so the horizontal dimension must need to be the first one in the list. This is a great solution . thanks

aggr

( if(Step_Forecast_Packs<>above(Step_Forecast_Packs) and [Alias number] = above([Alias number]),rgb(200,200,200)),YearWeek, [Customer Name],[Alias number],[Item name],Version)