Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
@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.
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.
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
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
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.