0 Replies Latest reply on Jul 2, 2014 12:10 PM by Randy Davis

    Create predefined filters for Custom 4-4-5 Calendar

    Randy Davis

      I am trying to create predefined filters that will serve as column headings and allow a user to select a predefined range (Prior Week, Prior Period, Prior 13 Weeks) from a parameter.

       

      Here is an image (see attached xls for cleaner image) of what I am trying to create and I have also attached a Packaged Workbook with sample data.

       

      P&L Mock-Up.JPG

       

       

      The time periods are all driven off of the Week ID Table, which has fields for Week ID, Fiscal Year, Period ID, Period of Year and Week of Year and Week of Period.

       

      In the example above:

      "Prior Week" should be [Week ID] = Max([Week ID]) - 1

      "Prior Week - Prior Year" should be [Week ID] = Max([Week ID]) - 53

      "Prior Period" should be [Period ID] = Max([Period ID]) - 1

      "Prior Period - Prior Year" should be [Period ID] = Max([Period ID]) - 13

      "Last 13 Weeks" should be [Week ID]<= Max([Week ID]) AND [Week ID] >= Max([Week ID]) -12

      "Last 13 Weeks - Prior Year" should be [Week ID]<= Max([Week ID]) -52 AND [Week ID] >= Max([Week ID]) -64

       

      Thanks in advance for any help.  Randy