Conditional Formatting

[#1]

I have a pivot table as below

2345617654124325

Time	Scenario	Call Rate	Volume	Expenses	Base	Rate	Cost	Repair	Header 10
Nov 2011	Actuals	6.2%	$2345K	2.3%	$2312K
Nov 2011	Budget	9.2%	2348
Jan 2012	Actuals	4.5%	77456
Jan 2012	Budget	3.1%	25656
Mar 2012	Actuals	4.9%	67456
Mar 2012	Budget	2.3%	24356
May 2012	Actuals	9.8%
May 2012	Budget	1.2%
July 2012	Actuals	1.7%
July 2012	Budget	2.8%

I want to add conditional formatting to Actuals value of all the metrics(Call Rate, Volume, Expenses....) based on the corresponding Budget value for the same month.

For example if Nov 2011.Actuals.Call Rate> Nov 2011.Budget.Call Rate then I want the background to be red. How do we perform this kind of conditional expression in Qlikview.

Request any thought or help in order to get this possible. Is this out of scope?

SS

Time	Scenario	Call Rate	Revenue   Dollars(K$)	Repair Volume	Call Volume	CpR	CpC	Ops spend(K$)	WIB	Ops $ per WIB	Repair Expenses	Call Expenses
201211	Actuals	9.73%	$163,706.56	53726	136893.66	$99.96	$4.50	$6,764.05	0	$0.00	$5,370.55	$616.09
201211	Budget	41.04%	$172,465.02	50896.10059	134510.0565	$73.68	$5.15	$6,237.34	0	$0.00	$3,749.88	$693.13
201212	Actuals	8.18%	$226,980.83	52823	137891.488	$103.41	$4.14	$6,680.41	0	$0.00	$5,647.49	$520.97
201212	Budget	41.04%	$172,465.02	50896.10059	134510.0565	$73.68	$5.15	$6,237.34	0	$0.00	$3,749.88	$693.13
201301	Actuals	8.28%	$212,192.14	51966	141405.03	$99.19	$3.79	$5,729.87	712529.6667	$106.63	$4,705.84	$441.46
201301	Budget	41.04%	$172,465.02	50896.10059	134510.0565	$73.68	$5.15	$6,237.34	655468.7574	$114.19	$3,749.88	$693.13
201302	Actuals	9.13%	$140,636.29	38776	136120.92	$102.07	$3.71	$5,384.03	1064786.667	$66.85	$4,300.22	$580.61
201302	Budget	39.73%	$182,547.43	48370.77076	127783.6233	$71.44	$5.19	$5,697.41	988345.6622	$73.55	$3,228.26	$674.82
201303	Actuals	9.23%	$201,355.67	49642	100152.6636	$98.06	$4.04	$5,862.06	1399152	$48.53	$4,760.40	$501.97
201303	Budget	38.47%	$182,547.43	48370.77076	127783.6233	$69.13	$5.24	$5,697.41	1326365.093	$53.17	$3,228.26	$674.82
201304	Actuals	8.73%	$225,627.55	49031	135010.45	$99.46	$4.29	$5,608.02	1744413.667	$38.65	$4,610.49	$509.95
201304	Budget	37.24%	$182,547.43	48370.77076	127783.6233	$66.74	$5.28	$5,697.41	1669527.05	$40.95	$3,228.26	$674.82
201305	Actuals	7.96%	$162,609.50	50982	136176.35	$93.96	$4.20	$5,702.65	2105486.667	$32.63	$4,690.00	$547.81
201305	Budget	37.03%	$190,344.43	50668.93855	134153.8168	$65.03	$5.16	$5,585.59	2020371.221	$33.62	$3,129.69	$661.57

[rwunderlich · Accepted solution]

In the Background Color for each exppression: For example, in the "Repair Volume" expression.

=if(Scenario = 'Actuals'

AND sum([Repair Volume]) > sum({<Scenario={Budget}>}TOTAL<Time>[Repair Volume])

,LightRed()

)

-Rob

http://masterssummit.com

http://robwunderlich.com

[#3]

Thanks Rob for the reply. It did work.

I also want to add multiple background colors depending on conditions. It does not seem to work. Is this the way we do it? or there is an another way to do? Also I am not able to find else function while writing these conditions.

=if(Scenario = 'Actuals'
AND sum([Repair Volume])> 0 and  sum([Repairs Ops])<=sum({<Scenario={Budget}>}TOTAL<Time>[Repair Volume])
,LightGreen()
)

=if(Scenario = 'Actuals'
AND sum([Repair Volume])>sum({<Scenario={Budget}>}TOTAL<Time>[Repair Volume]) and  sum([Repair Volume])<=1.05*sum({<Scenario={Budget}>}TOTAL<Time>[Repair Volume])
,LightCyan()

)

=if(Scenario = 'Actuals'
AND sum([Repair Volume])>sum({<Scenario={Budget}>}TOTAL<Time>[Repair Volume]) and  sum([Repair Volume])>=1.05*sum({<Scenario={Budget}>}TOTAL<Time>[Repair Volume])
,LightRed()

)

Thanks,

Shikha

[rwunderlich]

ELSE is the third parmater to IF(). It can be another IF(). So for example.

=if(some condition, red()

,if(another condition, blue()

,if(another condition, green()

,black()   // The final "otherwise" condition

)))

-Rob