2023-05-13
Analytical Calendar
Date Conversion and Calculation
Date calculation is very complicated. First
of all, they should be converted into the same granularity before calculation
can be performed. Using different granularity for calculation may generate unexpected
results as understandings of date granularities can be idiosyncratic.
For example, what should be the date to
compare the data on the date 2022-02-29, 1 month before? It is 2022-01-29, as the
month component moves 1 month before the date. By the same token, to compare 2022-03-31 1
month ago, what is the result? It is 2022-02-29. Since after moving the month
component, the date does not exist, i.e. 2022-02-31. Then, it is assumed the
month-end date, i.e. 2022-02-29. It is not easy to be aware of all of these
calculations because there might be already functions provided in the BI tool
for all these implicit situations. The date conversion is, in fact, the
shifting to the date component.
However, there are date calculations
actually not available in the tools. For example, week calculation. A week before means 7 days before. However, for
the case 1 week before 2022-W1, it has two scenarios:
If the complete week model is used:
·
2022-W1 includes 2022-01-02 to
2022-01-08.
·
One week before is 2021-W52 including
2021-12-26 to 2022-01-01.
If the incomplete week model is used:
·
2022-W1 includes only
2022-01-01.
·
One week before is 2021-W53
including 2021-12-26 to 2022-12-31.
The calculation is not straightforward, it
depends on which week model is used.
There is another example like YTD. Taking
2021-03-02 YTD to compare 1 year before. The analysis period is from 2021-01-01
to 2021-03-02 and the comparison period is from 2020-01-01 and to 2020-03-02. It
seems no problem. If it is to consider
the date 2020-02-29, there is 1 day difference. For a fair comparison it should have the date
range 2020-01-01 to 2020-03-01. When performing analysis for a business, 1 day
might make a big difference in revenue.
All these kinds of calculations on the fly
are complicated and difficult to control. It is certain that the formula is
always going into errors because of handling large number of situations and
exceptions.
It is, therefore, best to pre-calculate and
confirm all the date models to be used beforehand.
Previous Section <== 005 - Analytical Calendar - Date Range
No comments:
Post a Comment