Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
So, I have an absence table, which I'd like to load into a calendar type table plotting how much resource we have available on a particular day.
Absence Table:
EMP_NO | DATE_FROM | DATE_TO | DURATION |
1 | 01/01/2023 | 03/01/2023 | 3 |
2 | 03/01/2023 | 04/01/2023 | 2 |
3 | 02/01/2023 | 02/01/2023 | 1 |
1 | 15/02/2023 | 15/02/2023 | 1 |
2 | 22/02/2023 | 23/02/2023 | 2 |
3 | 23/02/2023 | 25/02/2023 | 3 |
My plan is to load each date in the next 2 weeks from today from a master calendar. So if I was to pick a particular date from this calendar on chart/filter, a table will be able to show me how many people are on annual leave.
So for example from the table above:
On the 01/01/2023, it would show Employee 1 on leave.
On the 02/01/2023, it would show Employees 1 and 3 on leave.
I've been playing with the data but honestly don't even know where to start with it.
you have to generate the records which comes under that date range
try below
Load EMP_NO, DATE_FROM, DATE_TO, date(DATE_FROM + IterNo() - 1) as ABSENCE_DATE
From table_name
while DATE_FROM + IterNo() - 1 <= DATE_TO;
Regards,
Prashant Sangle
you have to generate the records which comes under that date range
try below
Load EMP_NO, DATE_FROM, DATE_TO, date(DATE_FROM + IterNo() - 1) as ABSENCE_DATE
From table_name
while DATE_FROM + IterNo() - 1 <= DATE_TO;
Regards,
Prashant Sangle
Prashant - that's fantastic, thank you so much, that really put things in perspective for me and gives me a great start to this analysis. Really appreciate you taking the time to provide the solution.