topic Re: Background color in pivot table in QlikView

Background color in pivot table

Fri, 21 Feb 2014 14:55:42 GMT

Hi.

I have a pivot table with two dimensions, YearMonth and SystemCode. I use the following expression to get rolling 13 for some system codes and it works fine:

count({$<PeriodCounter={">$(#=max(PeriodCounter-14))<=$(#=max(PeriodCounter)-1)"},SystemCode={'114','744','746','113','992','118','121','125'}>} SystemCode)

But I would like to add background colors (red/green) if the system codes are over or under goal. The issue is that the system codes I use have differnt goals.

I tried to create a if-statement for the background color for the expression like (below only example for system code 114):

if(count({$<PeriodCounter={">$(#=max(PeriodCounter-14))<=$(#=max(PeriodCounter)-1)"},SystemCode={'114'}>} SystemCode) < only({$<PeriodCounter={">$(#=max(PeriodCounter-14))<=$(#=max(PeriodCounter)-1)"},KPI_Heading={"Goal_114"}>} KPI_Value), RGB(68,185,68),RGB(226,83,83))

This gives me a pivot table with all the system codes and not the correct color on system code 114.

Is there anyone that have some suggestions?

Re: Background color in pivot table

Gysbert_Wassenaar — Fri, 21 Feb 2014 16:16:39 GMT

You probably need a pick-match combination in your expression:

pick(match(SystemCode,114,744,....,125)

, if(count(${<PeriodCounter= ... >}SystemCode)< only({<...>}KPI_Value), rgb(...), rgb(...) ) // SystemCode 114

, if(count(${<PeriodCounter= ... >}SystemCode)< only({<...>}KPI_Value), rgb(...), rgb(...) ) // SystemCode 744

, ....

, if(count(${<PeriodCounter= ... >}SystemCode)< only({<....>}KPI_Value), rgb(...), rgb(...) ) // SystemCode 125

)

Can you post an example document?

Re: Re: Background color in pivot table

Tue, 25 Feb 2014 12:21:32 GMT

I have attached an example where you can find the first pivottable that is correct but without background color and the other table I have start with pick(match...) but it doesn't that well.