How to calculate the Nth of the Month

Question & Answer
, , ,
1

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:

240401_NthWeekofMonth
240401_NthWeekofMonth748×911 32.6 KB

Best Regards,
Boram

2

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
3

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

4

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

5

Hi @iamboram
could you solve your issue?
BR

6

Sorry for the belated reply to all.
I’ve made the timetable to fix this problem.

Thank you so much for taking care of my issue.

Best regards,
Boram