1 Reply Latest reply on May 6, 2016 10:17 AM by diego.medrano.12

    Arrays

    Kate Moyer

      I have an array that I use in excel but am having problems replicating it in tableau.  I am new to tableau and am hoping someone can help break it down for me.

       

       

      This is the table with the array result.  I then take this and place it into a heat map.

       

       

      T1T2Array
      0:30:001:30:000
      1:30:002:30:000
      2:30:003:30:000
      3:30:004:30:000
      4:30:005:30:000
      5:30:006:30:000
      6:30:007:30:000
      7:30:008:30:001
      8:30:009:30:003
      9:30:0010:30:004
      10:30:0011:30:004
      11:30:0012:30:003
      12:30:0013:30:004
      13:30:0014:30:003
      14:30:0015:30:002
      15:30:0016:30:003
      16:30:0017:30:003
      17:30:0018:30:001
      18:30:0019:30:000
      19:30:0020:30:000
      20:30:0021:30:000
      21:30:0022:30:000
      22:30:0023:30:000
      23:30:0024:30:000

       

      This is a sample of the data that the array looks at and the formula that is used:

       

      =COUNT(IF(($F$29:$F$58>=A3)*($F$29:$F$58<=B3)+($G$29:$G$58>=A3)*($G$29:$G$58<=B3)+($F$29:$F$58<=A3)*($G$29:$G$58>=B3)>0,1,""))

       

       

      start timeend time
      7:4012:15
      9:009:16
      9:2010:05
      10:0813:15
      10:2510:34
      11:1014:51
      12:4016:44
      13:0213:45
      15:5016:21
      16:2217:20
      17:0418:20

       

       

      The end result (of multiple days in a month) looks like this (with the first two rows shifted 2 cells over):

       

        

      3/13/23/33/43/53/63/7
      TUEWEDTHUFRISATSUNMON
      0:30:001:30:000000000
      1:30:002:30:000000000
      2:30:003:30:000000000
      3:30:004:30:000000000
      4:30:005:30:000000000
      5:30:006:30:000000000
      6:30:007:30:000000001
      7:30:008:30:001332005
      8:30:009:30:003754006
      9:30:0010:30:004553004
      10:30:0011:30:004444004
      11:30:0012:30:003453006
      12:30:0013:30:004443008
      13:30:0014:30:003523005
      14:30:0015:30:002325003
      15:30:0016:30:003336002
      16:30:0017:30:003234002
      17:30:0018:30:001212000
      18:30:0019:30:000111000
      19:30:0020:30:000000000
      20:30:0021:30:000000000
      21:30:0022:30:000000000
      22:30:0023:30:000000000
      23:30:0024:30:000000000