How to calculate the Nth of the Month

Hi, community members!

I’m struggling with how to make a calculated field which is about the Nth of Month.
Please refer to the attached image below.

The starting day is every Monday and I want to make Nth of the Month like this.
I tried to use the ‘extract’ function ‘WD’, but it didn’t make it.

A dataset is quite simple and we only have two columns.

  1. base_dt (ex. 20240301, 20240302, …, 20240430)
  2. sales_amt

Please let me know if there’s a good way to apply. :smiley:

Best Regards,
Boram

Hi @iamboram
As you mixed month and week I don’t get it.

So Nth of a month would be just the day number, right? “extract” function “DD”.

But your month is “starting on every Monday”. What does that mean?

BR

1 Like

Hi @ErikG
I would like to make a group like “1st Week of April”, “2nd Week of April” with those periods I colored on the calendar above.

If there’s any way to apply, please let me know :smiley:

Best Regards,
Boram

Hi @iamboram ,

Can you try something like this to get the month wise week number:
ceil((extract(‘WD’,truncDate(‘MM’,{reporting date}))-1 + extract(‘DD’,{reporting date}))/7) ?

you can then concatenate this to fetch the required 1st week of April.

You may need to modify this to suit the requirements e.g. for months starting on Saturdays, the number would come differently.

Thanks,
Prantika