topic Conditional color based on % to Total in QlikView

Conditional color based on % to Total

dwh1104 — Wed, 15 Jun 2011 16:46:03 GMT

I am able to get the % to total values to display properly in my pivot table. Now, I would like to display the following background color formatting as it is in Excel. ([PS Department Group] and [KPI Month] (going across) are both dimensions.

Red where the % for individual month is less than TOTAL % for all months.

Green where the % for individual month is greater than TOTAL % for all months.

Any help is greatly appreciated!

Thanks,

Dan

Conditional color based on % to Total

johnw — Wed, 15 Jun 2011 17:16:12 GMT

What is your expression? How is the chart defined?

Conditional color based on % to Total

dwh1104 — Wed, 15 Jun 2011 17:24:16 GMT

If(GetCurrentField([Date Comparison])='KPI Event Month'

sum({<KPI={'Visit Encounters'},

[KPI Event Year]={$(=Only([KPI Event Year]))}>}[KPI Amount])/

sum({<KPI={'Visit Encounters'},

[KPI Event Year]={$(=Only([KPI Event Year]))}>}TOTAL<[KPI Event Month],[KPI Forecast Unit Short]>[KPI Amount]),

If(GetCurrentField([Date Comparison])='KPI Event Quarter',

sum({<KPI={'Visit Encounters'},

[KPI Event Year]={$(=Only([KPI Event Year]))}>}[KPI Amount])/

sum({<KPI={'Visit Encounters'},

[KPI Event Year]={$(=Only([KPI Event Year]))}>}TOTAL<[KPI Event Quarter],[KPI Forecast Unit

Short]>[KPI Amount])))

Conditional color based on % to Total

prieper — Wed, 15 Jun 2011 21:32:25 GMT

Apply your conditions as Background Colour, you find it under Properties - Expressions and then open the small "+"-sign.

HTH
Peter

Conditional color based on % to Total

dwh1104 — Thu, 16 Jun 2011 13:06:48 GMT

Peter,

I know how the colored background works...What I need help with is getting the colors to show up based on each month's % to Total compared to ALL month's % to Total.

Thanks,

Dan

Conditional color based on % to Total

Thu, 16 Jun 2011 14:53:21 GMT

you can use the functions Dimensionality() OR ColumnNo() to calculate the Column total for each Column.

Ex.: IF(Dimensionality() <> 0, SUM(Values)/100, <new_expression>)

Re: Conditional color based on % to Total

dwh1104 — Thu, 16 Jun 2011 16:12:26 GMT

Dimensionality() and Column() will not help me, because the Month going across is actually a dimension. (There is not a separate expression for each month.)

Re: Conditional color based on % to Total

Thu, 16 Jun 2011 17:47:19 GMT

I understand but the function dimensionality() return the value "0" when the column is "TOTAL"

You can use to calculate the total for each row. See this example;

Re: Conditional color based on % to Total

dwh1104 — Fri, 17 Jun 2011 18:23:28 GMT

Eduardo,

I can see how this has the potential to work. Now I know the difference between Column() and ColumnNo() and how it can be used. Thanks!

Now I just need to figure out how to apply it to my issue.

If you could help decipher what the following expression is doing, it would be a great help.

IF(Sum(Value) < Last(RANGESUM(Before(SUM(Value),0,ColumnNo()))) / COUNT(Total Distinct Date), RGB(255,0,0),

IF(Sum(Value) > Last(RANGESUM(Before(SUM(Value),0,ColumnNo()))) / COUNT(Total Distinct Date), RGB(0,255,0)))

Re: Conditional color based on % to Total

Mon, 20 Jun 2011 13:28:07 GMT

Hi,

The sintaxe "RANGESUM(Before(SUM(Value),0,ColumnNo() ) )" return the value acumulate for each column:

30 65 30 -> 30 95 125

The function "LAST()" replicate the last value for each row: 125

COUNT(Total Distinct Date): returns the number of month (column) because we need to calculate the avg for all value in the table:

"41,666666"

After just need to verify the valeu with this result to demonstrate the color that you want:

IF 30 > 41,666666 return GREEN()

IF 30 < 41,666666 return RED()

Re: Conditional color based on % to Total

dwh1104 — Mon, 20 Jun 2011 16:15:44 GMT

Thanks Eduardo. I was able to work out the background color expression for my issue, based on the expression you provided.