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

Siddhesh_Bandekar · August 2022

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 ?

Thanks!

MichelleH · August 2022

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

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

MichelleH · August 2022

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

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

Siddhesh_Bandekar · August 2022

Thanks @MichelleH. This is what I was looking for.

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

Best Answer

Answers

Categories