topic Re: Color coding a pivot cell based on value on the earlier cell value in QlikView

Color coding a pivot cell based on value on the earlier cell value

sayadutt — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

My data looks like below. My requirement is to highlight the cells based on previous value and mark them either Red or Amber.

If the current cell is less than its previous cell, then the value of the current cell should be Red,

If the current cell is equal to its previous cell, then the value of the current cell should be Amber,

otherwise default.

Can you please suggest what should be my text color under Edit Expression of a Pivot Table

Data:

Team	ReleaseMonth	Targeted Regression Scenarios	Automated	Manual
A	Dec 19	147	122	25
A	Jan 20	155	122	33
A	Feb 20	152	100	52
B	Dec 19	2793	2747	46
B	Jan 20	3121	3051	70
B	Feb 20	105	50	55

What I want is: a Pivot Table

Team	ReleaseMonth	Targeted Regression Scenarios	Automated	Manual
A	Dec 19	147	122	25
A	Jan 20	155	122	33
A	Feb 20	152	100	52
B	Dec 19	2793	2747	46
B	Jan 20	3121	3051	70
B	Feb 20	105	50	55

Re: Color coding a pivot cell based on value on the earlier cell value

Taoufiq_Zarra — Tue, 21 Jan 2020 22:23:27 GMT

I propose this solution :

on load Script :

Data: LOAD rowno() as ID,previous(Manual) as P_Manual,previous("Targeted Regression Scenarios") as P_Targeted,previous(Automated) as P_Automated,* INLINE [ Team, ReleaseMonth, Targeted Regression Scenarios, Automated, Manual A, Dec 19, 147, 122, 25 A, Jan 20, 155, 122, 33 A, Feb 20, 152, 100, 52 B, Dec 19, 2793, 2747, 46 B, Jan 20, 3121, 3051, 70 B, Feb 20, 105, 50, 55 ];

then in expression -> Text Color for [Targeted Regression Scenarios]

if([Targeted Regression Scenarios]<P_Targeted,red(),if([Targeted Regression Scenarios]=P_Targeted,yellow()))

same of other expression for example :

if(Automated<P_Automated,red(),if(Automated=P_Automated,yellow()))

if(Manual<P_Manual,red(),if(Manual=P_Manual,yellow()))

output :

Re: Color coding a pivot cell based on value on the earlier cell value

sayadutt — Tue, 21 Jan 2020 23:46:14 GMT

I somehow managed to solve this by using above() function