Magic ETL - how to structure Multiple fields that represent monlthy budget amounts

Gojo

Hi i have a question concerning structuring variable fields using group by. So currently I have 12 variable fields, where a user can input a budget amount in each field, for a total of 12 months. the budgeting can start during any given month, so i have a starting month field, that the user must use to enter the budget for the first month, and then the user can optionally enter budgets for up to 11 additional months after entering the amount in the starting month field. Since the fields are used to enter budget amount, the way I know what month is the starting month is date range that the user inputs, based on that, I know which month the starting month will be. Because, the budget may vary month to month, that is why these monthly budget fields were created.

My question is, since these fields are not labeled as a certain month, instead, they are laebled budget m1, budget m2, budget m3, budget m4, budget m5, etc. what is the best way to structure the values in etl, so that the data is handled as intended?

david_cunningham

@Gojo

I'm not sure exactly what your question is. What do you mean by "so that the data is handled as intended"? Is there a particular problem that you're currently facing with this approach?

Gojo

Hi David,

My apologies for being unclear. My question is what woud be the best way to set up such a process in the ETL, so that it works properly?

Gojo

Hi David.

Just to follow up here, as mentioned above, I have twelve fields that list monthly budgets amounts, one for each month in the year. the 12 fields are labeled as such, budget month 1, budget month 2, budget month 3, etc. I also have a start date and end date range, these dates ranges will differ from user to user. The idea is that whatever the start date is, will automatically be associated with budget month 1, and the next month in that range will be associated with budget month 2, etc. So my question really is the best way to group the budget fields, and associate that date range with each of the budget fields, that are required by the date range.

e.g. if the date range is 6/12/2024 - 10/30/2024, then the user would fill out 5 budget fields (since the event will spread through 5 months. So budget month 1 could have the amount $5000, and since the flight starts midway through the first month, (6/12/2024), the budget would be spread over those 19 days, and budget month 2 field would be assigned to the following month 7/1/2024 - 1/31/2024, and so on for budget month 3 8/1/2024 - 8/31/2024. and so on.

Would would the best way to approach setting something like this up in the etl. Would you suggest first grouping the budget month 1 - budget month 12 together, then using the date dimensions table for the date range? How would you associate the budget month fields to the date range, so that domo knows which months to assign the budget fields to?

Thank you for your help.