Help with MDX Statement

christian.papalia · January 7, 2022, 3:34pm

Hi Team,

I am hoping you might be able to help me please with a MDX statement in Apliqo UX. I would like to display three stacked elements - Year, Month, Week. The dimensions are called Years, Months and Weeks.

I have managed to successfully display a starting point which comes from three different attributes (ETD_Year, ETD_Month and ETD_Week) from another dimension called BSM Order. Here is the statement that works:
{
{[Years].[Years].[$<<CXMD.BSM Order.BSM Order::ETD_Year>>]} *
{[Months].[Months].[$<<CXMD.BSM Order.BSM Order::ETD_Month>>]} *
{[Weeks].[Weeks].[$<<CXMD.BSM Order.BSM Order::ETD_Week>>]}
}

The thing that I am struggling with is displaying in the next column to the right of the above the next week. Lets say that the statement above returns 2021 for the year, Jun for the month and Week 52 for the week… I would like the next column (the next week) to display 2022 for the year, Jul for the month and Week 01 for the week.

I have the info to return the next week’s details in a cube called Relative Time (Weeks). The dimensions (in order) for this cube are Years, Months, Weeks, Relative Time Measure, Time Measure. The Time Measures store the elements Year, Month and Week, while the Relative Time Measure stores string elements -1, 0, 1 to take you one week back (-1) or one week forward (1). So, the DBRW formula in Excel to obtain the year of the next week would be DBRW(“cxmd: Relative Time (Weeks)”, “2021”, “Jun”, “Week 52”, “1”, “Year”).

I have tried the following MDX statement:
{
{[Years].[Years].
[Relative Time (Weeks)].(
[Years].[$<<CXMD.BSM Order.BSM Order::ETD_Year>>],
[Months].[$<<CXMD.BSM Order.BSM Order::ETD_Month>>],
[Weeks].[$<<CXMD.BSM Order.BSM Order::ETD_Week>>],
[Relative Time Measure].[Relative Time Measure].[1],
[Time Measure].[Time Measure].[Year])
}
*
{[Months].[Months].
[Relative Time (Weeks)].(
[Years].[$<<CXMD.BSM Order.BSM Order::ETD_Year>>],
[Months].[$<<CXMD.BSM Order.BSM Order::ETD_Month>>],
[Weeks].[$<<CXMD.BSM Order.BSM Order::ETD_Week>>],
[Relative Time Measure].[Relative Time Measure].[1],
[Time Measure].[Time Measure].[Month])
}
*
{[Weeks].[Weeks].
[Relative Time (Weeks)].(
[Years].[$<<CXMD.BSM Order.BSM Order::ETD_Year>>],
[Months].[$<<CXMD.BSM Order.BSM Order::ETD_Month>>],
[Weeks].[$<<CXMD.BSM Order.BSM Order::ETD_Week>>],
[Relative Time Measure].[Relative Time Measure].[1],
[Time Measure].[Time Measure].[Week])
}
}

And I receive the following error:

Here is the error in case the .png file hasn’t uploaded.

Status:

400

Message:

Syntax error at or near: ’ [Years].[2022], [Months].[Feb], [Weeks] … ', character position 138

MDX:

SELECT {{TM1SubsetToSet( [BSM Cost Codes].[BSM Cost Codes], UX BSM Cost Codes)}} ON ROWS, {{ {[Years].[Years]. [Relative Time (Weeks)].( [Years].[2022], [Months].[Feb], [Weeks].[Week 34], [Relative Time Measure].[Relative Time Measure].[1], [Time Measure].[Time Measure].[Year]) } * {[Months].[Months]. [Relative Time (Weeks)].( [Years].[2022], [Months].[Feb], [Weeks].[Week 34], [Relative Time Measure].[Relative Time Measure].[1], [Time Measure].[Time Measure].[Month]) } * {[Weeks].[Weeks]. [Relative Time (Weeks)].( [Years].[2022], [Months].[Feb], [Weeks].[Week 34], [Relative Time Measure].[Relative Time Measure].[1], [Time Measure].[Time Measure].[Week]) } }*{ [BSM Rate Measures].[BSM Rate Measures].[UOM], [BSM Rate Measures].[BSM Rate Measures].[Currency], [BSM Rate Measures].[BSM Rate Measures].[Forex Cost Per Unit] }} ON COLUMNS FROM [BSM Rates Table] WHERE ( [BSM Version].[BSM Version].[Override],[Port Discharge].[Port Discharge].[VNHPH],[Port Transhipment].[Port Transhipment].[SGSIN],[Port Load].[Port Load].[AUSYD],[BSM Container Types].[BSM Container Types].[20FQ],[BSM Incoterms].[BSM Incoterms].[CFR],[BSM Carrier].[BSM Carrier].[HAPAG],[BSM Freight Mode].[BSM Freight Mode].[SEA])

I have also tried:
{
{[Relative Time (Weeks)].(
[Years].[$<<CXMD.BSM Order.BSM Order::ETD_Year>>],
[Months].[$<<CXMD.BSM Order.BSM Order::ETD_Month>>],
[Weeks].[$<<CXMD.BSM Order.BSM Order::ETD_Week>>],
[Relative Time Measure].[Relative Time Measure].[1],
[Time Measure].[Time Measure].[Year])
}
*
{
[Relative Time (Weeks)].(
[Years].[$<<CXMD.BSM Order.BSM Order::ETD_Year>>],
[Months].[$<<CXMD.BSM Order.BSM Order::ETD_Month>>],
[Weeks].[$<<CXMD.BSM Order.BSM Order::ETD_Week>>],
[Relative Time Measure].[Relative Time Measure].[1],
[Time Measure].[Time Measure].[Month])
}
*
{
[Relative Time (Weeks)].(
[Years].[$<<CXMD.BSM Order.BSM Order::ETD_Year>>],
[Months].[$<<CXMD.BSM Order.BSM Order::ETD_Month>>],
[Weeks].[$<<CXMD.BSM Order.BSM Order::ETD_Week>>],
[Relative Time Measure].[Relative Time Measure].[1],
[Time Measure].[Time Measure].[Week])
}
}
and I receive a very similar error to the above.

Apologies for the long email, however, I am hoping there is enough info above to explain the issue. Your assistance would be very much appreciated.
Regards,
Christian

cw-ch-scott · January 7, 2022, 3:56pm

Hi @christian.papalia
I think here a picture might be worth 1000 words. If you could post a screenshot mock up of the grid arrangement you are trying to achieve then it woudl really help to understand what you are attempting to do and suggest a solution.

christian.papalia · January 7, 2022, 9:37pm

Thanks for the response… Seems like I could have saved myself plenty of time by posting some pictures!! So here goes…

Where the x is where I would like to see 2022 Feb Week 35 and between the two vertical lines UOM, Currency and Forex Cost Per Unit.

I used this statement to make 2022 Feb Week 35 appear:
{
{[Years].[Years].[$<<CXMD.BSM Order.BSM Order::ETD_Year>>]} *
{[Months].[Months].[$<<CXMD.BSM Order.BSM Order::ETD_Month>>]} *
{[Weeks].[Weeks].[$<<CXMD.BSM Order.BSM Order::ETD_Week>>]}
}

Here is a screenshot of where I have used the above statement and setup of this grid in ApliqoUX.

I also put together what I would like the page to do in Excel. The cells in orange in the table are what I am trying to achieve.
BSM Rates Table Example.xlsx (13.0 KB)

Cheers,
Christian

FelipeFonseca · January 8, 2022, 10:23am

Hi, @christian.papalia

When you retrieve a cell value from a cube, it’s either a value or a string, not a member.

For this reason, you have to transform the string retrieved from the cube [Relative Time (Weeks)] into a member or a set. Thus I recommend using the functions StrToMember or StrToSet.

I believe the following COLUMN statement will work for you, and you will just need to replace the fixed members of Years, Months and Weeks with Apliqo UX variables.

{		
		{ /* Current period set: Year x Month x Week */
			[Years].[Years].[2022]
			* 
			[Months].[Months].[Feb]
			*
			[Weeks].[Weeks].[Week 34]
		}
		+ /* Union of two sets: current and next periods */
		{ /* Next period set: Year x Month x Week */
			StrToSet(
				'{[Years].[Years].['
				+ [Relative Time (Weeks)].( /* Retrieve next Year string*/
					[Years].[Years].[2022], 
					[Months].[Months].[Feb], 
					[Weeks].[Weeks].[Week 34], 
					[Relative Time Measure].[Relative Time Measure].[1], 
					[Time Measure].[Time Measure].[Year]
					)
				+ ']}'
			)
			* 
			StrToSet(
				'{[Months].[Months].['
				+ [Relative Time (Weeks)].( /* Retrieve next Month string*/
					[Years].[Years].[2022], 
					[Months].[Months].[Feb], 
					[Weeks].[Weeks].[Week 34], 
					[Relative Time Measure].[Relative Time Measure].[1], 
					[Time Measure].[Time Measure].[Month]
					)
				+ ']}'
			)
			* 
			StrToSet(
				'{[Weeks].[Weeks].['
				+ [Relative Time (Weeks)].( /* Retrieve next Week string*/
					[Years].[Years].[2022], 
					[Months].[Months].[Feb], 
					[Weeks].[Weeks].[Week 34], 
					[Relative Time Measure].[Relative Time Measure].[1], 
					[Time Measure].[Time Measure].[Week]
					)
				+ ']}'
			)
		}
		* /* Product with measures set */	
		{	[BSM Rate Measures].[BSM Rate Measures].[UOM],
			[BSM Rate Measures].[BSM Rate Measures].[Currency],
			[BSM Rate Measures].[BSM Rate Measures].[Forex Cost Per Unit] 
		}
}

I believe your MDX view query will look like this:

SELECT 
	{TM1SubsetToSet([BSM Cost Codes], UX BSM Cost Codes)}
	ON ROWS,
	{		
		{ /* Current period: Year x Month x Week */
			[Years].[Years].[2022]
			* 
			[Months].[Months].[Feb]
			*
			[Weeks].[Weeks].[Week 34]
		}
		+ /* Union of two sets: current and next periods */
		{ /* Next period: Year x Month x Week */
			StrToSet(
				'{[Years].[Years].['
				+ [Relative Time (Weeks)].( /* Retrieve next Year string*/
					[Years].[Years].[2022], 
					[Months].[Months].[Feb], 
					[Weeks].[Weeks].[Week 34], 
					[Relative Time Measure].[Relative Time Measure].[1], 
					[Time Measure].[Time Measure].[Year]
					)
				+ ']}'
			)
			* 
			StrToSet(
				'{[Months].[Months].['
				+ [Relative Time (Weeks)].( /* Retrieve next Month string*/
					[Years].[Years].[2022], 
					[Months].[Months].[Feb], 
					[Weeks].[Weeks].[Week 34], 
					[Relative Time Measure].[Relative Time Measure].[1], 
					[Time Measure].[Time Measure].[Month]
					)
				+ ']}'
			)
			* 
			StrToSet(
				'{[Weeks].[Weeks].['
				+ [Relative Time (Weeks)].( /* Retrieve next Week string*/
					[Years].[Years].[2022], 
					[Months].[Months].[Feb], 
					[Weeks].[Weeks].[Week 34], 
					[Relative Time Measure].[Relative Time Measure].[1], 
					[Time Measure].[Time Measure].[Week]
					)
				+ ']}'
			)
		}
		* /* Product with measures set */	
		{	[BSM Rate Measures].[BSM Rate Measures].[UOM],
			[BSM Rate Measures].[BSM Rate Measures].[Currency],
			[BSM Rate Measures].[BSM Rate Measures].[Forex Cost Per Unit] 
		}
	}
	ON COLUMNS
FROM 
	[BSM Rates Table]
WHERE (
	[BSM Version].[BSM Version].[Override],
	[Port Discharge].[Port Discharge].[VNHPH],
	[Port Transhipment].[Port Transhipment].[SGSIN],
	[Port Load].[Port Load].[AUSYD],
	[BSM Container Types].[BSM Container Types].[20FQ],
	[BSM Incoterms].[BSM Incoterms].[CFR],
	[BSM Carrier].[BSM Carrier].[HAPAG],
	[BSM Freight MODE].[BSM Freight MODE].[SEA]
	)

I hope this can help you.