Create macro to identify the start of month

[Former Member]

Hi experts,

I need to create a macro that will identify if the current week belong to a particular month. Example week 14-17 is for month 4 while week 18-22 for month 5. Can you post the formula or fucntions that I can use.

Thanks

Regards,

Lhon

[rajkj · Accepted solution]

Hi Lon,

Try the following pseudo code to get month.

STEP1: 1..n iterations

MONTH( BUCKET_BDATE(

Any key figure row ) )

Thanks,

Rajesh

[former_member209769]

Hi Lon,

As you would have already noticed from Rajesh's reply, you can find out the month based on a date, so in Rajesh's example, he has shared a way in which you find out the month based on start date of your weekly periods (or it could be periods in some other periodicity). Similarly, you could also use MONTH( BUCKET_EDATE( )) to find out the month based on the end date of the bucket in your planning view.

This MONTH function would return the number of month as an integer e.g. April = 4.

Similarly, you coudl also use YEAR() function to get the year corresponding to a date (e.g. bucket beginning date or bucket ending date).

Also note here that for the argument row (whatever Key Figure row you use above), you should double-click on the row, and select "row attributes" as "data source".

If this MONTH/YEAR function doesn't meet your requirements, then you could share some more details of the requirement so that it might be possible for someone to suggest solutions.

Thanks - Pawan