topic Re: Color in QlikSense Pivot Table in App Development

Color in QlikSense Pivot Table

ShellyG — Sat, 16 Nov 2024 18:29:18 GMT

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

Re: Color in QlikSense Pivot Table

Anil_Babu_Samineni — Tue, 16 Jun 2020 10:13:47 GMT

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

Re: Color in QlikSense Pivot Table

ShellyG — Tue, 16 Jun 2020 10:27:04 GMT

Hello Anil,

Thank you for your suggestion.

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

Re: Color in QlikSense Pivot Table

brunobertels — Tue, 16 Jun 2020 11:45:50 GMT

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

Re: Color in QlikSense Pivot Table

ShellyG — Tue, 16 Jun 2020 14:45:57 GMT

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!

Re: Color in QlikSense Pivot Table

brunobertels — Tue, 16 Jun 2020 16:22:30 GMT

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

Re: Color in QlikSense Pivot Table

ShellyG — Mon, 22 Jun 2020 07:26:25 GMT

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!