topic background color for pivot table problem in QlikView

background color for pivot table problem

bdunphy — Tue, 07 Feb 2012 17:27:20 GMT

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:

(YearWeek>=Version,

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

Condtion:

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

background color for pivot table problem

swuehl — Tue, 07 Feb 2012 19:38:05 GMT

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,

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

background color for pivot table problem

bdunphy — Wed, 08 Feb 2012 11:18:28 GMT

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)

background color for pivot table problem

swuehl — Wed, 08 Feb 2012 11:36:22 GMT

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)

background color for pivot table problem

Wed, 08 Feb 2012 11:37:17 GMT

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

background color for pivot table problem

bdunphy — Wed, 08 Feb 2012 11:51:53 GMT

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