2 Replies Latest reply on Aug 5, 2016 4:07 AM by jenn.ng

    See Year over year values but only for latest date using index

    jenn.ng

      I have Year over year data and their corresponding fiscal week number. I'm trying to create a table where i can keep only the latest fiscal week revenue number for 2016 and its corresponding revenue in the same fiscal week for 2015.

       

      my data:

          

      fiscal_yearfiscal_weekrevenue
      20151123
      20152224
      20153300
      20154350
      20161100
      20162150
      20163NULL
      20164NULL

       

      current plot:

      Screen Shot 2016-08-05 at 2.33.41 AM.png

       

      what i want:

      instead of manually setting the fiscal week to the latest week that is not null, can i do it automatically?

      i tried to create a calculated field to be used as the index integer:

       

      if [Fiscal Year]=2016 and not isnull([Revenue])

      then [index]=max([Fiscal Week])-1

      END

      but it does not work

       

      Screen Shot 2016-08-05 at 2.25.35 AM.png