3 Replies Latest reply: Jul 16, 2013 3:34 AM by Rob Wunderlich RSS

    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
        • Re: Conditional Formatting
          Rob Wunderlich

          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

          • 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