Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
bdunphy
Contributor III
Contributor III

background color for pivot table problem

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

Accepted Solutions
swuehl
MVP
MVP

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)

 

View solution in original post

5 Replies
swuehl
MVP
MVP

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
Contributor III
Contributor III
Author

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

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)

 

Not applicable

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
Contributor III
Contributor III
Author

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