Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
dwh1104
Creator II
Creator 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.

Conditional Color.gif

Any help is greatly appreciated!

Thanks,

Dan

1 Solution

Accepted Solutions
Not applicable

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

View solution in original post

10 Replies
johnw
Champion III
Champion III

What is your expression?  How is the chart defined?

dwh1104
Creator II
Creator II
Author

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

prieper
Master II
Master II

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

HTH
Peter

dwh1104
Creator II
Creator II
Author

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

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

dwh1104
Creator II
Creator II
Author

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

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;

dwh1104
Creator II
Creator II
Author

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

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