Color in QlikSense Pivot Table

[ShellyG]

Dear all,

I have an issue with the font color in a pivot table. The pivot table basically has countries as rows and years as columns. I have the below calculation, which I am using to calculate growth in forecast:

(sum({<Year = {'$(=max(Year))'}>}aggr(sum(Units), Year, Country))+sum(aggr(sum(ForecastUnits),Year, Country)))
/(sum(total <Country>{<Year = {'$(=max(Year))'}>}aggr(sum(Units), Year, Country)))

The results in the pivot table is in percentages and they go from 0 to approximately 120%. 

I want to make a gradient font color for the percentages to be something like from 0 to 35% - dark red, from 35% to 65% - light red, from 65% to 100% - light blue and above 100% - dark blue

Basically I need to pain the numbers in the pivot table gradually from red to blue. I tried with colormix but it doesn't seem to work. Any ideas? 

Thank you in advance! 

Best Regards,

Shelly 

[Anil_Babu_Samineni]

May be try this?

If((sum({<Year = {'$(=max(Year))'}>}aggr(sum(Units), Year, Country))+sum(aggr(sum(ForecastUnits),Year, Country)))
/(sum(total <Country>{<Year = {'$(=max(Year))'}>}aggr(sum(Units), Year, Country)))>=Num(0, '#,#0%') and (sum({<Year = {'$(=max(Year))'}>}aggr(sum(Units), Year, Country))+sum(aggr(sum(ForecastUnits),Year, Country)))
/(sum(total <Country>{<Year = {'$(=max(Year))'}>}aggr(sum(Units), Year, Country)))<=Num(0.35, '#,#0%'), DarkRed(),
If((sum({<Year = {'$(=max(Year))'}>}aggr(sum(Units), Year, Country))+sum(aggr(sum(ForecastUnits),Year, Country)))
/(sum(total <Country>{<Year = {'$(=max(Year))'}>}aggr(sum(Units), Year, Country)))>Num(0.35, '#,#0%') and (sum({<Year = {'$(=max(Year))'}>}aggr(sum(Units), Year, Country))+sum(aggr(sum(ForecastUnits),Year, Country)))
/(sum(total <Country>{<Year = {'$(=max(Year))'}>}aggr(sum(Units), Year, Country)))<=Num(0.65, '#,#0%'), LightRed(),
If((sum({<Year = {'$(=max(Year))'}>}aggr(sum(Units), Year, Country))+sum(aggr(sum(ForecastUnits),Year, Country)))
/(sum(total <Country>{<Year = {'$(=max(Year))'}>}aggr(sum(Units), Year, Country)))>Num(0.65, '#,#0%') and (sum({<Year = {'$(=max(Year))'}>}aggr(sum(Units), Year, Country))+sum(aggr(sum(ForecastUnits),Year, Country)))
/(sum(total <Country>{<Year = {'$(=max(Year))'}>}aggr(sum(Units), Year, Country)))<=Num(1, '##0%'), LightBlue(), DarkBlue())))

[ShellyG]

Hello Anil, 

Thank you for your suggestion.

I tried, but all the numbers go blue...

[brunobertels]

Hi  

with fixed color try this expression below 

Now if you want tu use colormix function to get a gradiant color you will have to use if () function for each step of your range using rvb() function and consider the max color of a step as the min color of the next step 

Try 

If( 

(sum({<Year = {'$(=max(Year))'}>}aggr(sum(Units), Year, Country))+sum(aggr(sum(ForecastUnits),Year, Country)))
/(sum(total <Country>{<Year = {'$(=max(Year))'}>}aggr(sum(Units), Year, Country)))

<='100%' ,lightblue() ,

If( 

(sum({<Year = {'$(=max(Year))'}>}aggr(sum(Units), Year, Country))+sum(aggr(sum(ForecastUnits),Year, Country)))
/(sum(total <Country>{<Year = {'$(=max(Year))'}>}aggr(sum(Units), Year, Country)))

<= '65%' , lightred() ,

If( 

(sum({<Year = {'$(=max(Year))'}>}aggr(sum(Units), Year, Country))+sum(aggr(sum(ForecastUnits),Year, Country)))
/(sum(total <Country>{<Year = {'$(=max(Year))'}>}aggr(sum(Units), Year, Country)))

<= '35%' , red() , blue() ))) 

[ShellyG]

Hello Bruno,

I tried but it doesn't seem to work properly.... All numbers are painted in blue still... Maybe it has something to do with the number formatting, so I tried different variations, but still nothing. 

Can you give me an advice on how to use the other option you mentioned, the rvb()?

Thank you!

[brunobertels · Accepted solution]

Hi 

Have a look at this link 

Qlik-Sense-Color-Range-Theme-Approach 

it may help you on how to use colormix1 and colormix2 in pivot table 

colormix 1 will give you a shade between 2 colors ( min value = 0 ; and max value = 1 ) 

colormix2 give the same but with 3 colors between min value =-1 , 0 = median, +1 = max value per column 

I read a post longtime ago about a solution to use colormix1 function with if statement to create a range ( for example less then 100% less than 60% , less than 30 % ) it sounded very well but very difficult to reproduce. I will try again if i can found it in the forum 

Regards 

[ShellyG]

ColorMix2( (hrank(total (sum({}Year)-1)'}>}aggr(sum(Units), Year, Country))+sum(aggr(sum(ForecastUnits),Year, Country))) /(sum(total <Country>{}Year)-1)'}>}aggr(sum(Units), Year, Country))) )/(NoOfColumns(TOTAL)/2))-1, rgb(68,119,170), rgb(255,0,0), rgb(144,202,249))

I did this and it seems like it is working. Not exactly the way I want it to but it is ranking the values and coloring them accordingly. 🙂 Thank you very much for your help!