How to manage the leap year in comparative analysis
1. Abstract
In an application life cycle it is not unusual to come across leap year management, moreover when comparative analysis are put in place. Accurate handling of leap years ensures data consistency across different timeframes, preventing discrepancies when comparing results from different years or performing calculations involving dates.
2. Context
Leap years add an extra day to February, affecting how dates are calculated throughout the year. In most of the applications, accurate date handling is essential. Ignoring leap years can lead to inconsistencies when comparing data across different timeframes. For example, imagine comparing sales figures from February 2023 to February 2024 (bissextile). Without proper leap year management, the application might treat them as having the same number of days, skewing the results.
3. Content
This document will walk through how to best approach the leap year management in Board applications when the data are stored on daily basis and cubes structured accordingly, to avoid any potential data inconsistencies.
3.1 Common use cases comparing periods with leap year
In Board applications where data is stored at the day level, performing comparative analysis can be impacted by leap years due to the presence of an extra day in February. This can lead to several challenges: a common use case for Board applications is year-over-year (YoY) analysis with the usage of the time functions. Ignoring leap years can cause significant discrepancies leading to misinformed decision-making since there is the possibility that a wrong set of data is retrieved when pointing to a leap year with a time function such as the previous year or previous period.
In the next chapters, we will see in details which are the most frequent challenges that a Board application can face when a comparative multi-year analysis is put in place.
Assumption: all the following recurrent issues arise when the cubes in object are structured with a Day granularity, so Board rightly consider the 2nd month of the leap years with 29 days instead of 28 with all the all relevant consequences. If the time dimensions are more aggregated (month, quarter, year and so on…) there are no cautions to be taken into account considering that the presence of an extra day in a leap year February becomes insignificant and doesn't affect results.
3.1.1 Daily basis analysis
Daily analysis could be the need to compare the daily figures of a certain month with the corresponding previous year using the previous year time function. Let’s assume retrieving the 2024 from the 2025:
Figure 1– Previous Year with the leap year
In this example the usage of the previous year time function is not appropriate as the content of February 29th 2024 is completely ignored since Board has no possibility to show its value on any February 2025 days.
The usage of the Period offset features would better fits in this instance. This time function shifts the time by a specified number of periods, following the cube structure.
Figure 2 – Offset function
Since the cube in the example is structured by day, the offset must be set on -365 or -366 (depending on the needs) to better compare the figures of February 2024 and February 2025
Figure 3 – Offset in a leap year
In the figure above it is observed that using the -366 offset the sales of the 29/02/2024 will be shown on the 01/03/2025, and the 01/02/2024 on the 01/02/2025. Vice versa with the -365 offset the 29/02/2024 value will be shown on 28/02/2025 but the 01/02/2024 on the 31/01/2025.
Unlike with the other time functions using the offset is possible to retrieve the 29th Feb value at least, and then decide where expose it playing with the range.
3.1.2 Aggregated analysis
The same type of issue occurs when there’s the need to compare data in a year-over-year analysis at higher level such month, quarter or year itself.
Figure 4 – Month analysis
In this example can be observed that the Time functions Previous Year and Offset -366 behave similarly, ignoring the last day of February 2024 (612,080) which is the difference with the Feb.24 Total. Likewise the Offset-365 has a difference of 597,454 that is the value of the first day of February 2024 that is ignored (Check Figure 3 for better comprehension).
Exactly the same for a year to year compare:
Figure 5– Year analysis
Retrieving the previous year value from the 2025 will generate a discrepancy of 612,080 from the Total of 2024 that once again is the sales value of the 29th February 2024.
3.2 How to better approach the leap year in Board
Overcoming this limitation described in the previous chapter might be easier than expected. In most of the cases could be enough add a version of the interested cube with the month time dimension instead of the day one, and then align the versions.
Figure 6 - Versioning
With this structure of the cube Board will be able to ignore the day level of analysis when a more aggregated entity is in the axis, retrieving the right set of data pointing to a leap year with the time functions:
Figure 7 – Month analysis with month version cube
Note that now the offset is set to -12 since has to go back at month level, not at day anymore.
The same results with the year compared, now the previous year function called from the 2025 perfectly match with the 2024 total:
Figure 8 – Year analysis with month version cube
Another option that could be leveraged is to create a dedicated reporting cube for this kind of comparative analysis structured by month instead of day. The result it’s exactly the same since Board considers the month of February as a unique item ignoring the additional day in the leap years. Here below is an example that shows the difference between a Sales cube structured by day and by months using another time function: the Yearly Moving Total, which retrieves the cumulated value of the course of the previous 12 months:
Figure 9 – Yearly moving total with a day cube
Figure 10 – Drill by day
In the figure 9 the Total of the 12 months differs from the yearly moving total of Jan25 by 597,454. Drilling the corresponding month by day can be noted that it’s the exact value of the 1st of January 2024 that Board ignores counting back 365 days.
Performing the same analysis with a cube structured by month the function retrieves the same amount of the monthly Total with no discrepancies, due to the aggregated calculation made by month.
Figure 11 – Yearly moving total with a monthly cube
4. Conclusion
In conclusion, the importance of leap year management is essential in Board applications that store data at the day level to ensure data accuracy, reliable analysis, and efficient resource allocation, especially when performing comparative analysis. With the insights provided above any type of misleading results can be avoided showing the expected figures in comparative analysis.