4 Replies Latest reply on Jul 11, 2016 7:47 PM by Mikey Michaels

    Calculated Field help - I'm going crazy

    Mikey Michaels

      Hello All,

      Thanks for taking a look.

      On the attached, I'm trying to calculate the number of records over a specified period of time based off a parameter called "date parameter". I have created two tabs: One is a manual table called "Manual", where I manually selected the dates and the number of records during the Jan 16 - Mar 16 time frame. Obviously I do not want to have to manually select  new dates as I add new data to my Excel source file.

       

      The second tab is called "Calculated Field" and contains a calculated field that is called "EASY in range- Previous (1)".  This calculated field is to look back until the Jan - Mar 16 time frame and calculate the number of records automatically and will update when a new month of data is added. Here is the problem:

       

      As of right now my CF reads:

       

      if ([Month]) >= DATEADD('month',-5,[Date parameter]) and ([Month])<= DATEADD('month',-3,[Date parameter]) then

      [Number of Records]

      end

       

      However this only returns Jan 16 - Feb 16

       

      If I adjust the formula to read:

       

      if ([Month]) >= DATEADD('month',-5,[Date parameter]) and ([Month])<= DATEADD('month',-2,[Date parameter]) then

      [Number of Records]

      end

       

      This returns Jan - April 16

       

      How can I adjust this formula to look at Jan - Mar 16!? I'm going crazy over here