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

Siddhesh_Bandekar

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

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

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

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