topic Re: Plotting dates into a calendar table in App Development

Plotting dates into a calendar table

krisslax — Wed, 06 Dec 2023 08:56:47 GMT

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.

Re: Plotting dates into a calendar table

PrashantSangle — Thu, 07 Dec 2023 06:31:20 GMT

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

Re: Plotting dates into a calendar table

krisslax — Thu, 07 Dec 2023 07:04:14 GMT

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.