I need Help with tracking bookings based on date ranges

[#1]

I need to do two things with my data.

First I need to be able to show what's been booked on a rolling weeks basis. I've tried the below but can seem to get it to work:

=Sum({$<BOOKINGDATE=P({1<BOOKINGDATE={">=$(=Today()-7)<$(=Today())"}>}BOOKINGDATE)>}NETNET)

The next thing I need to get is get a forward booked position. If I choose a BOOKINGDATE this year what were the bookings at the same time last year?

I have no idea where to start with this one.......

Thanks in advanced

[maxgro · Accepted solution]

sum({<BOOKINGDATE={">=$(=date(Today()-7))<=$(=Today())"}>} NETNET) 

[ramoncova06]

try with this

=Sum({$<BOOKINGDATE={">=$(=Today()-7)<$(=Today())"}>}>}NETNET)

and this should give you the rolling twelve months

=Sum({$<BOOKINGDATE={"=$(=addmonth(Today()),-12)"}>}>}NETNET)

[MarcoWedel]

Hi,

your set expression could either

only expand expressions that evaluate to a date format

or

use the expression search syntax and compare the BOOKINGDATE field with pure numerics to avoid date format issues (I prefer the latter).

Possible solutions could be:

hope this helps

regards

Marco

[#5]

Thanks - that works perfectly!

Any idea how I can get the Forward position for last year.

Basically for whatever the user selection is for BOOKINGDATE - what was the equivalent revenue (NETNET) we had booked at that same point in time last year.

I tried adapting your expression as below but no luck

sum({<BOOKINGDATE={">=$(=date(BOOKINGDATE-730))<=$(=date(BOOKINGDATE-365))"}>} NETNET)