topic How to convert month lines to day lines in a script in App Development

How to convert month lines to day lines in a script

SupportActiveProfessionals — Sat, 23 Nov 2024 19:07:03 GMT

Hi,

I have a datasource with the following columns
Date, Budget (in euro)
1/8/2024,1000
1/9/2024,1500
1/10/2024,2000
The date is the first day of that month (DD/MM/YYYY). The amount is a total budget amount for that month

Now I want to load this data in a script and generate lines PER DAY.
So the end result of the table after executing the script should be

Date, Budget
1/8/2024, 32.26 (Calculated as 1000/31 as august has 31 days)
2/8/2024, 32.26
3/8/2024, 32.26
31/8/2024, 32.26
1/9/2024, 50 (calculated as 1500 / 30 as september has 30 days)
etc.
How do I do that?

Re: How to convert month lines to day lines in a script

barnabyd — Sun, 24 Nov 2024 20:24:47 GMT

G'day,

There are a couple off ways to solve this. If you have a calendar table, you can left join the calendar into your data table and that will duplicate all the rows to the number of days in each month. If you don't have a calendar table, then you can use this strategy:

DailyBudget: load Date + iterno() - 1 as [Daily Budget Date] , Budget / [Days In Month] as [Daily Budget] while iterno() <= [Days In Month] ; load Date , Budget , floor( monthend( Date ) ) as [Days In Month] resident MonthlyBudgetTable ;

I'm not sure if this code is perfect, but the gist of it is to use the monthend() function to find the days in the month and the 'load ... while ...' to iterate over the month.

I hope this makes sense. Cheers, Barnaby.

Re: How to convert month lines to day lines in a script

MarcoWedel — Sun, 24 Nov 2024 20:38:56 GMT

maybe using

day( monthend( Date ) ) as [Days In Month]

instead