Count if date dimension between 2 dates

[deeroma93]

Hi!

I have the below data set (current Table) which I'm trying in the front end via expression to create a table or graph (expected result)

This is for an accommodation/occupancy dashboard. Basically i need to select a given date and count how many people had a booking and graph it to see what days were the busiest.

thank you!!

Current Table

ID	Start	End
1	01/02/2022	03/02/2022
2	03/02/2022	04/02/2022
3	03/02/2022	06/02/2022

Expected result

Date	Count
01/02/2022	1
02/02/2022	1
03/02/2022	3
04/02/2022	2
05/02/2022	1

[sidhiq91]

@deeroma93  Could you please let me know on what basis are you going to get the count? If you could elaborate a little bit more it would help me to resolve the issue.

[deeroma93]

Hi! thanks for helping.

there should only be a count if the date is between a start and end date.

So for ID 1, that person stayed between the 1/02/2022 - 03/02/2022

so in a table or a chart, 1/02/2022 and 02/02/2022 would count ID 1.

So basically i need to be able to see at any given date, how many people were staying at the hotel.

[MayilVahanan]

HI 

Need to generate the date between start and end.

Load *, Date(Start+IterNo()-1) as Date While Start+IterNo()-1 <= End;
LOAD * INLINE [
ID, Start, End
1, 01/02/2022, 03/02/2022
2, 03/02/2022, 04/02/2022
3, 03/02/2022, 06/02/2022
];

In Front end, Try like below

[deeroma93]

Thank you!! I did think of this, but since i'm working with such a large data set, going back multiple years and 1000's of records, i thought that the stored data might blow out quite a lot.

is there anyway this can be done as a set analysis/expression

[MayilVahanan]

In front end, how u can generate the intermediate date?
And also, u are generating the dates based on Primary key.. so it won't impact much.