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

# need help summing the data for a date range

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.

• ###### 1. Re: need help summing the data for a date range

Hey Paul,

The Year() function returns and integer which you are comparing to a date.

Try

IF [Date Signed] > #2016/06/30# THEN [Grand Total \$]

ELSEIF [Date Signed] < #2016/07/01# THEN [Grand Total \$]

ELSE NULL

END

Regards,

Ivan

• ###### 2. Re: need help summing the data for a date range

Ivan - thanks for this.  I see what you mean. Taking out the YEAR part makes the formula valid.  But it doesn't seem to limit the bookings to just what occurred inside the date range.  My intention is to limit the bookings to what should be inside this date range.

• ###### 3. Re: need help summing the data for a date range

Well it looks like i've answered my own question here.  Putting in an AND operator seems to do the trick.  I am now able to put the two measures on a bullet chart quite easily.  Good to go.

IF [Date Signed] > #2015/06/30#
AND [Date Signed] < #2016/06/30# THEN [Grand Total \$]
ELSE null
END