There are often scenarios where we need to build complex formula.
This happens frequently in cases where we need to carry out date calculations. For example, there may be a requirement to display a list of all the weekends in year, or the requirement to display the first day of each quarter.
These types of calculations can be very difficult and a simple alternative not to overlook, is to build lists of static data and to import those into an app. In this post, we'll walk through an example.
Example - How to calculate the ISO week number
Let’s suppose we want to display the week number that corresponds to a date. We can search online for formulas, and apply formulas such as the one that AUPowerapps posted here.
What are the challenges of complex formulas?
There's nothing wrong with the formula above, and it's a perfectly good technique. The main disadvantage is that it isn’t very maintainable. For
apps that we develop in a team, it can be very difficult for other app builders to
understand,modify and to adapt the date calculation logic.
To give an example, it's not uncommon for businesses to want to modify the date ranges. In 2021, week one starts on Janary 4th. There may be a requirement to change the start date so that
week one begins on January 1st. With this change in logic, we can better label
a target date of 2nd January 2021 as “Week 1 2021”, rather than “Week 53
The problem is that it's difficult to work out the logic and to adapt the existing formula so that it takes into account the change in date range at the start and end of each year.
How to simplify this requirement by using static data
To simplify this task, a 'hack' or workaround is to build a list of static values and to import those into an app (or to make the data available through an online data connection). By using this technique, we can carry out this calculation in a more maintainable way.
The screenshot beneath shows a list of static date ranges in Excel. We can easily build this type of list by using the 'fill down' feature in Excel.
We can import this spreadsheet through the data panel.
Once we add the static Excel data, we can easily retrieve week number by calling the LookUp function like so.
LookUp(WeekNumbers, inputDate >= StartDate And inputDate <=EndDate)
In cases where we need to carry out complex calculations, it can be much easier and more maintainable to use static data. This post provided a demonstration by showing how to calculate week numbers.