Conditional expressions

[#1]

Hi,

I am new to Qlikview and having problem performing some basic tasks. I desire to perform conditional formatting on the below table and having a hard time with it.

Time	Scenario	Call Rate	Revenue   Dollars(K$)	Repair Volume	Call Volume	CpR	CpC	Ops spend(K$)	WIB	Ops $ per WIB	Repair Expenses	Call Expenses
Nov 2012	Actuals	9.73%	$163,706.56	53726	136893.66	$99.96	$4.50	$6,764.05	0	$0.00	$5,370.55	$616.09
Nov 2012	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
Dec 2012	Actuals	8.18%	$226,980.83	52823	137891.488	$103.41	$4.14	$6,680.41	0	$0.00	$5,647.49	$520.97
Dec 2012	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
Jan 2013	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
Jan 2013	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
Feb 2013	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
Feb 2013	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
Mar 2013	Actuals	9.23%	$201,355.67	49642	100152.6636	$98.06	$4.04	$5,862.06	1399152	$48.53	$4,760.40	$501.97
Mar 2013	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
Apr 2013	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
Apr 2013	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
May 2013	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
May 2013	Budget	37.03%	$190,344.43	50668.93855	134153.8168	$65.03	$5.16	$5,585.59	2020371

I want to add multiple background colors to the measure fields(Call Rate, Revenue Dollars.....) depending on 3 conditions. It does not seem to work. Is this the way we do it? or there is an another way to do?

For example  I am writing the below query on the background expression of Repair Volume metric and it is not working. 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

[CELAMBARASAN · Accepted solution]

Hi,

In qlikview

If(condition, true stmt, false stmt)

Your expression will be

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

To optimize yours

=if(Scenario = 'Actuals',

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

,LightGreen()

if(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(sum([Repair Volume])>sum({<Scenario={Budget}>}TOTAL<Time>[Repair Volume]) and  sum([Repair Volume])>=1.05*sum({<Scenario={Budget}>}TOTAL<Time>[Repair Volume])

,LightRed()
))))

Hope it helps

Celambarasan.

Note: I didn't verified the expressions.