MAQL - Select all records that enclose a list of dates

Question

I'm a bit of a noob in MAQL so I would like to know if it is possible to do the following thing :

I got a list of reservations with a Start Date and a End Date

I would like to be able to compute a metric that would give me (for reservations that match some criteria) the number of reservations that include a given night.

In the end, I want my metric to exist for each date x:

MyMetric(x) = Number of reservations with Start > x and End < x

I tried to do the following request:

SELECT COUNT(Voucher) WHERE Date(Start)<THIS AND Date(End)>THIS

but I'm having trouble setting up a report saying "Well now I want THIS to take all the values in the calendar".

(Voucher is just an attribute from my "Reservation" dimension).

Anyone knows how I can proceed ? The user manual is very difficult to read for someone who does not have an extensive experience with MAQL.

Answer 1

I unfortunately don't believe that what you're trying to achieve is possible with the data model you have. The reason behind this is because each date is associated with a specific action, such as the date in which a trip begins and when it ends. In order for this to work exactly how you want, it would be necessary to add a date dimension in the project that has a record for each date between the Begin and End date (e.g. Event date=generic date to capture a range of different events or snapshot date).

Hope this helps!