Hey there,
We're changing our company's reporting calendar to use fiscal years and I'm looking for automation to update all the affected date dimensions in our apps. Unfortunately, most of our apps don't have a master calendar but include lots of transformations like the following in the script: Date(MonthStart([CreatedDate]), 'MM-YYYY') as CreatedMonthYear. The resulting fields are used in a multitude of charts and set expressions on the front-end and so I'm trying to keep the same fields but need to convert them to my new fiscal calendar.
I think there is an opportunity to write a common script that would loop through all tables and fields, find the date dimensions that need to be converted, and convert them to the fiscal calendar. Doing this by hand is possible in smaller apps but there is always a chance for human error and so I'm looking for an automated solution.
Found this routine suggested by @rwunderlich that enables cycling through all fields from all tables so my next step is to figure out the logic inside [DO THE THING] below. Here's how I envision the steps:
- Find date dimensions based on field names or date formats: e.g. match "...monthyear", "...quarteryear", "...year", etc. or match values saved as "MM-YYYY", "QX-YYYY", "YYYY", etc.
- For each of the found fields: create a service field storing dates from the respective periods (e.g. 1/1/2022 for 01-2022, 2/1/2022 for 02-2022, etc.) and then convert these dates into the same periods to match the fiscal year calendar (e.g. Date(MonthStart('1/1/2022',3), 'MM-YYYY') for a new ...MonthYear field, assuming a fiscal year Oct'22-Sep'23.
- Drop service fields, drop original date dimensions fields, rename new date dimensions fields to match the names of the original fields.
An ideal result is to end up with all the same date dimension fields but shifted to represent the fiscal year. Any experts here to try to convert this into an actual script?
For tableIdx = 0 to NoOfTables() - 1
Let vTableName = TableName($(tableIdx));
For fieldIdx = 1 to NoOfFields('$(vTableName)')
[DO THE THING]
Next fieldIdx
Next tableIdx
