How to generate the dynamic Monday of Previous Week from current date

[datamakesenseright]

I am new to Cognos Analytics. I want to use macro to generate the dynamic Monday of previous week from current date. The generated MUN looks like [Stats].[All Dates].[All Dates].[level004]->:[TM].[All Dates].[All Dates].[@MEMBER].[122023^20231204]. I used the following code and got error message.

[Stats].[All Dates].[All Dates].[level004]->:[TM].[All Dates].[All Dates].[@MEMBER].#sb(timestampMask(_add_days($current_timestamp,-6-timestampMask($current_timestamp, 'F'),'mm')+timestampMask(_add_days($current_timestamp,-6-timestampMask($current_timestamp, 'F'),'yyyy')+'^'+timestampMask(_add_days($current_timestamp,-6-timestampMask($current_timestamp, 'F'),'yyyymmdd'))#

Thanks in advances.

[MFGF]

I am new to Cognos Analytics. I want to use macro to generate the dynamic Monday of previous week from current date. The generated MUN looks like [Stats].[All Dates].[All Dates].[level004]->:[TM].[All Dates].[All Dates].[@MEMBER].[122023^20231204]. I used the following code and got error message.

[Stats].[All Dates].[All Dates].[level004]->:[TM].[All Dates].[All Dates].[@MEMBER].#sb(timestampMask(_add_days($current_timestamp,-6-timestampMask($current_timestamp, 'F'),'mm')+timestampMask(_add_days($current_timestamp,-6-timestampMask($current_timestamp, 'F'),'yyyy')+'^'+timestampMask(_add_days($current_timestamp,-6-timestampMask($current_timestamp, 'F'),'yyyymmdd'))#

Thanks in advances.

Hi,

I'm having trouble decoding what you are trying to do in your expression. You didn't say what error(s) you are getting - the error message can often be a good way to pinpoint where to start looking. There are a couple of immediately obvious issues I can see:

- The number of opening brackets ( is 10, whereas the number of closing brackets ) is 7. Every opening bracket for a macro function needs a corresponding closing bracket.
- The timestampMask function has specific masks it allows you to use in the second argument. From the help page: The format in "string_expression2" must be one of the following: 'yyyy', 'mm', 'dd', 'yyyy-mm', 'yyyymm', 'yyyy-mm-dd', 'yyyymmdd', 'yyyy-mm-dd hh:mm:ss', 'yyyy-mm-dd hh:mm:ss+hh:mm', 'yyyy-mm-dd hh:mm:ss.ff3', 'yyyy-mm-dd hh:mm:ss.ff3+hh:mm', 'yyyy-mm-ddThh:mm:ss', 'yyyy-mm-ddThh:mm:ss+hh:mm', 'yyyy-mm-ddThh:mm:ss.ff3+hh:mm', or 'yyyy-mm-ddThh:mm:ss.ff3+hh:mm' What are you trying to achieve with a mask of 'F' (which doesn't appear to be a valid mask)?

Cheers!

MF.

[datamakesenseright]

Hi MF,

Thank you for your assistance. Initially, I believed I could use the "F" symbol in the timestampMask function to extract the day of the week from the current date. I now realize that this approach doesn't work with timestampMask. Is there an alternative method to create a Member Unique Name (MUN) that includes the Monday of the previous week based on the current date, following the format [Stats].[All Dates].[All Dates].[level004]->:[TM].[All Dates].[All Dates].[@MEMBER].[122023^20231204]? In this format, 122023^20231204 represents the Monday in the MMYYYY^YYYYMMDD format.

Best

[MFGF]

Hi MF,

Thank you for your assistance. Initially, I believed I could use the "F" symbol in the timestampMask function to extract the day of the week from the current date. I now realize that this approach doesn't work with timestampMask. Is there an alternative method to create a Member Unique Name (MUN) that includes the Monday of the previous week based on the current date, following the format [Stats].[All Dates].[All Dates].[level004]->:[TM].[All Dates].[All Dates].[@MEMBER].[122023^20231204]? In this format, 122023^20231204 represents the Monday in the MMYYYY^YYYYMMDD format.

Best

Do you have weeks in the date hierarchy of your TM1 cube (Eg you might have Year, Quarter, Month, Week, Day levels)? If so, you could grab the parent member of the current day member (giving you the week), then take the first (or second) child member (depending on whether Sunday or Monday is the first day of the week).

If not, you are going to struggle here, I think. You could possibly create a parameter map that holds all dates for the foreseeable future along with the Monday MUN you would want to use for that date, then do a macro lookup on this, but it would be messy.

Date	MUN
2023-12-11	[Stats].[All Dates].[All Dates].[level004]->:[TM].[All Dates].[All Dates].[@MEMBER].[122023^20231211]
2023-12-12	[Stats].[All Dates].[All Dates].[level004]->:[TM].[All Dates].[All Dates].[@MEMBER].[122023^20231211]
2023-12-13	[Stats].[All Dates].[All Dates].[level004]->:[TM].[All Dates].[All Dates].[@MEMBER].[122023^20231211]
2023-12-14	[Stats].[All Dates].[All Dates].[level004]->:[TM].[All Dates].[All Dates].[@MEMBER].[122023^20231211]
2023-12-15	[Stats].[All Dates].[All Dates].[level004]->:[TM].[All Dates].[All Dates].[@MEMBER].[122023^20231211]
2023-12-16	[Stats].[All Dates].[All Dates].[level004]->:[TM].[All Dates].[All Dates].[@MEMBER].[122023^20231211]
2023-12-17	[Stats].[All Dates].[All Dates].[level004]->:[TM].[All Dates].[All Dates].[@MEMBER].[122023^20231211]
2023-12-18	[Stats].[All Dates].[All Dates].[level004]->:[TM].[All Dates].[All Dates].[@MEMBER].[122023^20231218]
2023-12-19	[Stats].[All Dates].[All Dates].[level004]->:[TM].[All Dates].[All Dates].[@MEMBER].[122023^20231218]
2023-12-20	[Stats].[All Dates].[All Dates].[level004]->:[TM].[All Dates].[All Dates].[@MEMBER].[122023^20231218]

Cheers!

MF.