5 Replies Latest reply: Jan 13, 2015 3:34 AM by Nayan Lalla RSS

    Expression Calculation on Pivot Table



      Currently our head office (Johannesburg) charges our branches a 10% markup on cost.  I have been task to determine what the branch's Cost of Sales would have been if it was at Johannesburg's Unit Cost.


      I am stuggling to calculate this expression (expression highlighted in red below & attached)


      Attached herewith is the Qlikview Model as well as the data (in excel format) on which my Qlikview model is based.


      Please can you assist.


      kind regards.\







      Financial Year2014
      Financial Period1
      BranchFisc.Per-Month1 (May)
      BloemfonteinSales Qty798
      Cost of Sales43,890
      Gross Profit35,910
      Unit Cost55.00
      Johannesburg COS0
      Cape TownSales Qty479
      Cost of Sales26,345
      Gross Profit21,555
      Unit Cost55.00
      Johannesburg COS0
      JohannesburgSales Qty13,160
      Cost of Sales658,000
      Gross Profit658,000
      Unit Cost50.00
      Johannesburg COS658,000
        • Re: Expression Calculation on Pivot Table
          Aaron Morgan

          Hi Nayan,


          Is the markup fixed? If so, surely the unit cost will always be 10% higher for you than for Joburg, so you can simply take whatever your cost of sales is and divide by 1.1 to give you the answer?


          Apologies if I've understood wrong!


          Edit: And if you need this to be correct for Joburg too, have the expression as:


          f(Branch='Johannesburg', [Cost of Sales], [Cost of Sales] / 1.1)