Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Conditional Formatting

I have a pivot table as below

2345617654124325
TimeScenarioCall RateVolumeExpensesBaseRateCostRepairHeader 10
Nov 2011Actuals6.2%$2345K2.3%$2312K
Nov 2011Budget9.2%2348
Jan 2012Actuals4.5%77456
Jan 2012Budget3.1%25656
Mar 2012Actuals4.9%67456
Mar 2012Budget2.3%24356
May 2012Actuals9.8%
May 2012Budget1.2%
July 2012Actuals1.7%
July 2012Budget2.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

TimeScenarioCall RateRevenue
  Dollars(K$)
Repair VolumeCall VolumeCpRCpCOps spend(K$)WIBOps $ per WIBRepair ExpensesCall Expenses
201211Actuals9.73%$163,706.5653726136893.66$99.96$4.50$6,764.050$0.00$5,370.55$616.09
201211Budget41.04%$172,465.0250896.10059134510.0565$73.68$5.15$6,237.340$0.00$3,749.88$693.13
201212Actuals8.18%$226,980.8352823137891.488$103.41$4.14$6,680.410$0.00$5,647.49$520.97
201212Budget41.04%$172,465.0250896.10059134510.0565$73.68$5.15$6,237.340$0.00$3,749.88$693.13
201301Actuals8.28%$212,192.1451966141405.03$99.19$3.79$5,729.87712529.6667$106.63$4,705.84$441.46
201301Budget41.04%$172,465.0250896.10059134510.0565$73.68$5.15$6,237.34655468.7574$114.19$3,749.88$693.13
201302Actuals9.13%$140,636.2938776136120.92$102.07$3.71$5,384.031064786.667$66.85$4,300.22$580.61
201302Budget39.73%$182,547.4348370.77076127783.6233$71.44$5.19$5,697.41988345.6622$73.55$3,228.26$674.82
201303Actuals9.23%$201,355.6749642100152.6636$98.06$4.04$5,862.061399152$48.53$4,760.40$501.97
201303Budget38.47%$182,547.4348370.77076127783.6233$69.13$5.24$5,697.411326365.093$53.17$3,228.26$674.82
201304Actuals8.73%$225,627.5549031135010.45$99.46$4.29$5,608.021744413.667$38.65$4,610.49$509.95
201304Budget37.24%$182,547.4348370.77076127783.6233$66.74$5.28$5,697.411669527.05$40.95$3,228.26$674.82
201305Actuals7.96%$162,609.5050982136176.35$93.96$4.20$5,702.652105486.667$32.63$4,690.00$547.81
201305Budget37.03%$190,344.4350668.93855134153.8168$65.03$5.16$5,585.592020371.221$33.62$3,129.69$661.57
1 Solution

Accepted Solutions

Re: Conditional Formatting

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 Replies

Re: Conditional Formatting

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

Not applicable

Re: Conditional Formatting

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

Re: Conditional Formatting

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

Community Browser