12 Replies Latest reply: Sep 29, 2016 7:52 PM by Josh Chang RSS

    pivot table use different dimension for total

    Josh Chang

      Hello,

       

      I'm trying to implement a pivot table which has the same appearance as this table:

      Two dimensions: ship week and Time2Fail

           

      Time2Fail12345678
      ship weekship countAccu ship count
      14411 1
      259 11 1
      3211 1
      4617 11 1
      52191   
      611301 2 11
      332011

       

      expression1: The ship count is how many units shipped in a ship week.

       

      expression2: The Accu Ship count is the accumulate ship count along the the ship week.

      e.g. accu ship count for ship week 1 is 4, for ship week 2 is 4+5=9 and for ship week 3 is 9+2= 11 and so on

       

      expression3: The last row is the sub total of the column Time2Fail, but only count up to the ship week.

      e.g. for the Time2Fail = 1 column, sub total = 1+1+1 = 3 (to ship week = 6)

      for the Time2Fail = 2 column, sub total = 1+1+1 = 3 (to ship week = 5)

      for the Time2Fail = 3 column, sub total = 1 +1=2 (to ship week = 4)

      for the Time2Fail = 4 column, sub total = 0 (to ship week = 3)

      for the Time2Fail = 5 column, sub total = 1 (to ship week = 2)

      for the Time2Fail = 6 column, sub total = 1 (to ship week = 1)

       

      based on the data:   

      ship weekIDTime2Fail
      110001
      110012
      11002
      110036
      219543
      21958
      219592
      21967
      219885
      328451
      32846
      413233
      41325
      414274
      41377
      41388
      413996
      567781
      56779
      667701
      68912
      613454
      64523
      64524
      69923
      699247
      69925
      699264
      69927
      69928

      8

       

      Thanks,

      Josh