2 Replies Latest reply on Nov 3, 2010 1:02 PM by David. Lewis

    Relative date filter using DATEADD('month',-1,NOW())  or something like it

    David. Lewis

      I have a figure showing year on year % change.  The years are on the row shelf, month on the column shelf, and a table calc for percent change comparing the present month/year with the month one year ago.  I have excluded the first year of the data by filter all null values. 

      The problem I would like to solve is the current month (and all future months for this year) appear as misleading low (or even -100%) because there is incomplete data, or no data yet.  I would like to display data for the current year ending on the month previous.  I tried creating a custom filter using range of dates, but there is no way to make that 'dynamic'.  By that I mean if I set it to 10/1/2010 it will be fixed there until I go in next month and change it.

       

      If I click on MONTH(fldDate) on the column shelf and try to filter that using a formula looking like: [flddate]<=DATEADD('month',-1,NOW()) I get an error saying the formula must be an aggregate calculation or refer only to this field.

       

      Basically, I'd like to eliminate the misleading table calc for the present month, and all future months.  Can anyone suggest a method?  Many thx.  d lewis