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

    Expression Calculation on Pivot Table

    Nayan Lalla

      Hi

       

      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.\

      Nayan

       

       

       

       

       

      Financial Year2014
      Financial Period1
      BranchFisc.Per-Month1 (May)
      BloemfonteinSales Qty798
      Sales79,800
      Cost of Sales43,890
      Gross Profit35,910
      GP%45.0%
      Unit Cost55.00
      Johannesburg COS0
      Cape TownSales Qty479
      Sales47,900
      Cost of Sales26,345
      Gross Profit21,555
      GP%45.0%
      Unit Cost55.00
      Johannesburg COS0
      JohannesburgSales Qty13,160
      Sales1,316,000
      Cost of Sales658,000
      Gross Profit658,000
      GP%50.0%
      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)