Leap year in Magic ETL

Effie
Effie Contributor
in Magic ETL

Hi all,


I'm working on an dataflow (Magic ETL) through which I calculate the MTD, QTD & YTD metrics for both This Year and Last Year data so that I compare the values for each one of these metrics. According to Finance, for leap years, when This Year the last day of Feb is the 28th and Last Year was the 29th, this extra day should be included in the metrics above.


I have created a flow of various steps which end up in 4 'if cases' (filters excluding one another) based on the following logic:
1. This Year (TY) is not 28/Feb or 29/Feb thus Last Year (LY) equals to same date last year, eg. TY=25/3/2019 -> LY=25/3/2018
2. TY = 28th Feb & LY = 28th Feb thus LY equals to same date last year, eg. TY=28/2/2019 -> LY=28/2/2018
3. TY = 28th Feb & LY = 29th Feb thus LY equals to same date last year plus 1, eg. TY=28/2/2021 -> LY=29/2/2020
4. TY = 29th Feb thus LY equals to same date last year minus 1, eg. TY=29/2/2020 -> LY=28/2/2019

 

The 4 filters along with the preceding steps take a significant amount of time to refresh especially when it comes to large input datasets. I was wondering if anyone else has used a similar logic on leap years in an ETL as I try to find a lighter approach. In my case, this part of the flow is to ensure I don't exclude the 29th of Feb in the MTD, QTD & YTD metrics and compare TY & LY data accurately.

Comments

  • Jarvis
    Jarvis Domo Employee

    Hey Effie,

    I think the best way to handle leap year would be to use Domo's Calendar card type. That will already account for leap year and everything else that might be needed in that regard. Outside of a Calendar card, I would suggest ensuring that your source data accounts for this change.

     

    Jarvis

  • Unknown
    Unknown

    Hi, @Effie ,

     

    Since you want to do this in MagicETL, you might consider creating CY/PY calendar before hand, and then referencing that table in your dataflow. You CY/PY calendar might look something like this:

    Date        | PY Date
    ---------------------------
    2017-02-28  | 2016-02-28
    2017-02-27  | 2016-02-27
    ...
    2016-03-01  | 2015-03-01
    2016-02-29  | 2015-02-28
    2016-02-28  | 2015-02-28
    2016-02-07  | 2015-02-27

    You could create such a table manually and upload using a file upload connector. Or you might build it within Domo using the calendar dataset in the Domo Dimensions connector and then creating your PY date field using logic similar to what you described.

     

    Then, you would simply join your CY/PY calendar in your Magic ETL dataflow and use it to look up the PY date.

     

    Hope that's helpful.

     

    Dan

Categories