3 Replies Latest reply on Feb 24, 2017 8:01 AM by Paul Morgan

    need help summing the data for a date range

    Paul Morgan

      I am trying to create a bullet chart showing this years bookings progress against last years bookings.  But i need to modify the date range to suit the school year.  So i can't just use a single date range via the filter shelf.  For example:

       

      this year = 7.1.2016 - 6.30.2017

      last year = 7.1.2015 - 6.30.2016

       

      I have one date field to work with named Date Signed.  And my bookings field is name Grand Total $.

       

      So my thought was that i'd just write a calculated field for each school year and then try to do the bullet chart with those.  But my formula is not working.  This might be because i'm not sure how to wrap a date in a formula (" or # what?).  Here is what i'm wrote to sum the '16/'17 school year:

       

      IF YEAR([Date Signed]) > #2016/06/30# THEN [Grand Total $]

      ELSEIF YEAR([Date Signed]) < #2016/07/01# THEN [Grand Total $]

      ELSE NULL

      END

       

      The error i get is "can't compare integer and date values.  Am i not denoting the dates correctly or is my logic wrong? I was thinking it could also be that my date part is not correct, but i'm not sure.

       

      Thanks in advance!