Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a pivot table as below
Time | Scenario | Call Rate | Volume | Expenses | Base | Rate | Cost | Repair | Header 10 |
---|---|---|---|---|---|---|---|---|---|
Nov 2011 | Actuals | 6.2% | 23456$2345K | 17654122.3% | $2312K | 4325||||
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 |
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
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
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,
ShikhaELSE 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