Skip to content

    • Magic ETL

    Magic ETL

    How to exclude incomplete week in calculated formulas (BM) while using fiscal calendar

    Member
    in Magic ETL

    Currently am using this formula to exclude current week:

    CASE
    WHEN WEEK(Snapshot Date) >= WEEK(maxSnapshotDate) AND
    YEAR(Snapshot Date) >= YEAR(maxSnapshotDate)
    THEN 0
    ELSE 1
    END

    however, it is excluding Sundays data. (I want to look at Fiscal calendar and it is selected under date settings)

    Best Answer

    • Coach
      Answer ✓

      @renee12345 Beast mode date functions always run off of Domo's default calendar, even if the Use Fiscal Calendar box is checked on that particular card. I'd suggest creating a spreadsheet upload dataset with columns for Date, Fiscal Week, Fiscal Month, and Fiscal Year that you can join to any datasets that require fiscal calendar calculations. Then you can re-create your calculation in ETL, but using comparisons to the corresponding fiscal periods instead of the WEEK(), MONTH(), and YEAR() functions.

    Answers

    • Member

      Nevermind, I figured it out

    • Coach
      Answer ✓

      @renee12345 Beast mode date functions always run off of Domo's default calendar, even if the Use Fiscal Calendar box is checked on that particular card. I'd suggest creating a spreadsheet upload dataset with columns for Date, Fiscal Week, Fiscal Month, and Fiscal Year that you can join to any datasets that require fiscal calendar calculations. Then you can re-create your calculation in ETL, but using comparisons to the corresponding fiscal periods instead of the WEEK(), MONTH(), and YEAR() functions.

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Quick Links

    Categories