11 Replies Latest reply: Apr 28, 2013 11:46 PM by danster8 RSS

    Need help with pivot table

      I need to show monthly product sales data by selected date range by the user.

      It is quite easy to create a pivot table with my required 2 dimensions, 1 being the ProductName and the other being the Month.

       

      So my pivot looks like

                    

       

      Product
      Jan13
      Feb13
      Mar13
      Apr13
      PartA      13      9      20       16
      PartB      43      21       28        40
      PartC      19       6       17          18
      PartD      27        51       32        29
      PartE     10      8       6         11
      PartF       17       22       23        37

       

      So far no problems.  The months shown above may vary depending on the range the user has selected, but it is basically the same.

       

      But what I really need is to show extra columns which show current quantities for each item.  These do not change depening on the month because they are static "as at now" or more correctly when the data was loaded.  I cannot work out how to get these extra columns to display without Qlikview thinking I need to show them by period.  I don't.  They are just another display column associated with the product. 

      What I need is this:

       

      product
      Current Stock Qty
      On backorder
      On Purch Order
      Jan 13
      Feb 13
      Mar 13
      Apr 13
      PartA        103        0               50   13    9    20    16
      PartB        56        0               100  43    21    28    40
      PartC      121        19               50   19    6    17    18
      PartD       0        5                200   27     51    32    29
      PartE    12        0               120   10    8    6    11

       

      The months need to change if the user selects, say July 10 to March 11.  But the 3 qantity columns do not change because they need to display up to date "now" data.

       

      How would I do this?

       

      Thanks in advance