Is there way to extract week of quarter from a date column?

edited August 2022 in Magic ETL

The default week() function extracts the week number from the start of the year and runs 1 to 53 or 54. I need to show week number within the quarter, running 1 to 13 or 14 within each qtr.

Is there a way to do this using beast mode or using the formula tile in Magic ETL ?


Best Answer

  • MichelleH
    MichelleH Coach
    Answer ✓

    Hi @Siddhesh_Bandekar you could try a beast mode like this, assuming the first 3 quarters have 13 weeks and Q4 takes the overflow:

    when WEEK(`Date`) >= 39 then WEEK(`Date`) - 38
    else MOD(WEEK(`Date`),13) + 1