Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

Not applicable

I need Help with tracking bookings based on date ranges

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

1 Solution

Accepted Solutions
MVP
MVP

Re: I need Help with tracking bookings based on date ranges

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

4 Replies
MVP
MVP

Re: I need Help with tracking bookings based on date ranges

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

ramoncova06
Valued Contributor III

Re: I need Help with tracking bookings based on date ranges

try with this

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

and this should give you the rolling twelve months

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

Re: I need Help with tracking bookings based on date ranges

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:

QlikCommunity_Thread_181287_Pic1.JPG

QlikCommunity_Thread_181287_Pic2.JPG

hope this helps

regards

Marco

Not applicable

Re: I need Help with tracking bookings based on date ranges

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)

Community Browser