2 Replies Latest reply on Jan 9, 2019 5:01 AM by Hau Hoang

    Relative Date Views

    Hau Hoang

      My workbook has data that is loaded monthly from business day minus 6 through business day 6.  The "Effective Date" field is my data's date field.  I am using the Relative Date tool to toggle between different dates depending on what I want to look at.  The calculated field that I created below is shown as a valid calculation, but the filter does not show all options from the calculated field.  It only shows "No Filter" and "Previous Month-end" in the filter.  Can anyone see what I am missing?  For example from screenshots below, I want to be able to select "Previous year" in the "Effective Date" filter using Relative Date, and then filter for "Previous Year-end" from the calculated field so that I only see data for 12/31/2018.

       

      //this filter option is to be combined with a Relative Date selection of "Previous month" to show only the last day of the previous month so I can view only the month-end balances rather than the sum of all daily balances in previous month

      IF  DAY([Effective Date]) = {FIXED MONTH([Effective Date]),YEAR([Effective Date]): MAX(DAY([Effective Date]))}

      THEN 'Previous Month-end'

       

      //this filter option is to be combined with a Relative Date selection of "Previous quarter" to show only the last day of the previous quarter so I can view only the quarter-end balances rather than the sum of all daily balances in previous quarter consisting of 3 months

      ELSEIF  DAY([Effective Date]) = {FIXED MONTH([Effective Date]),YEAR([Effective Date]): MAX(DAY([Effective Date]))} AND

      (MONTH([Effective Date]) = 12 OR MONTH([Effective Date]) = 9 OR MONTH([Effective Date]) = 6

      OR MONTH([Effective Date]) = 3)

      THEN 'Previous Quarter-end'

       

      //this filter option is to be combined with a Relative Date selection of "Previous year" to show only the last day of the previous year so I can view only the year-end balances rather than the sum of all daily balances in previous year consisting of 12 months

      ELSEIF  DAY([Effective Date]) = {FIXED MONTH([Effective Date]),YEAR([Effective Date]): MAX(DAY([Effective Date]))} AND

      MONTH([Effective Date]) = 12

      THEN 'Previous Year-end'

       

      //this filter option is to be combined with a Relative Date selection of  "This year" during the current calendar month but I don't want to include the current calendar month

      ELSEIF DAY([Effective Date]) = {FIXED MONTH([Effective Date]),YEAR([Effective Date]): MAX(DAY([Effective Date]))}

      AND [Effective Date] < DATETRUNC('month',TODAY())

      THEN 'Exclude Current Month'

       

      //this filter option is to be combined with a Relative Date selection of  "This year" during the current calendar month but I don't want to include the current calendar month and previous calendar month

      ELSEIF DAY([Effective Date]) = {FIXED MONTH([Effective Date]),YEAR([Effective Date]): MAX(DAY([Effective Date]))}

      AND [Effective Date] < DATEADD('month',-1,TODAY())

      THEN 'Exclude Last 2 Months'

       

      //this filter option is to allow for simple selection of Relative Date without any last day of the month filters

      ELSE 'No Filter'

       

      END

       

       

       

        • 1. Re: Relative Date Views
          Paul Cawford

          Hi Hua,

           

          I believe you are over complicating things for the filter.  Whilst the calculation is valid it will be difficult if not impossible to work in the way you would like.

           

           

          A much easier way to do this is by using parameters e.g.

           

          Create a parameter named [Relative Date] as a string with the values:

               'Previous Month-end'

               'Previous Quarter-end'

               'Previous Year-end'

               'Exclude Current Month'

               'Exclude last 2 months'

           

           

          Then create a variable that uses the parameter e.g. [Include Date]

           

          CASE [Relative Date]

          WHEN 'Previous Month-end' THEN [Effective Date]  = [Previous Month End*]

          WHEN 'Previous Quarter-end' THEN [Effective Date]  = [Previous Month End*]

          ...

          END

           

           

          Finally use the variable created above as the filter e.g. [Include Date] = True

           

           

          This should achieve your required effect.  * means you will need to insert and appropriate TRUE / FALSE calculations

           

           

          Hopefully this helps.

          Regards,

          Paul

          • 2. Re: Relative Date Views
            Hau Hoang

            Thanks, Paul.  I have resolved the issue.  It was due to the order of operations in the expression.  I changed to expression to the one below, removing the "exclude current month" and "exclude last 2 months" and kept those as separate filters.

             

            IF  DAY([Effective Date]) = {FIXED MONTH([Effective Date]),YEAR([Effective Date]): MAX(DAY([Effective Date]))} AND

            MONTH([Effective Date]) = 12

            THEN 'Previous Year-end'

             

            ELSEIF  DAY([Effective Date]) = {FIXED MONTH([Effective Date]),YEAR([Effective Date]): MAX(DAY([Effective Date]))} AND

            (MONTH([Effective Date]) = 12 OR MONTH([Effective Date]) = 9 OR MONTH([Effective Date]) = 6

            OR MONTH([Effective Date]) = 3)

            THEN 'Previous Quarter-end'

             

            ELSEIF  DAY([Effective Date]) = {FIXED MONTH([Effective Date]),YEAR([Effective Date]): MAX(DAY([Effective Date]))}

            THEN 'Previous Month-end'

             

            ELSE 'No Filter'

             

            END