QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Contributor II

Conditional color based on % to Total

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

Tags (1)
1 Solution

Accepted Solutions
Not applicable

Re: Conditional color based on % to Total

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

10 Replies
MVP

Conditional color based on % to Total

What is your expression?  How is the chart defined?

Contributor II

Conditional color based on % to Total

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

Honored Contributor II

Conditional color based on % to Total

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

HTH
Peter

Contributor II

Conditional color based on % to Total

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

Not applicable

Conditional color based on % to Total

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

Contributor II

Re: Conditional color based on % to Total

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

Not applicable

Re: Conditional color based on % to Total

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;

Contributor II

Re: Conditional color based on % to Total

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

Not applicable

Re: Conditional color based on % to Total

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