Hi, Just wondering whether this is a Workspace bug or a feature - and whether IBM have plans to address this.

The issue is that I have a limited period field with both discrete and YTD as elements however, I want to show BOTH of them, but controlled from a single filter.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
WITH
MEMBER [GPL-Value Type].[GPL-Value Type].[MTH] AS ([GPL-Value Type].[GPL-Value Type].[MMR Value],STRTOMEMBER((("[GPL-Year Period].[GPL-Year Period].[" + [GPL-Year Period].[GPL-Year Period].CURRENTMEMBER.NAME)) + "]")),FORMAT_STRING='#,##0;(#,##0);0'
MEMBER [GPL-Value Type].[GPL-Value Type].[YTD] AS ([GPL-Value Type].[GPL-Value Type].[MMR Value],STRTOMEMBER((((((("[GPL-Year Period].[GPL-Year Period].[" + LEFT([GPL-Year Period].[GPL-Year Period].CURRENTMEMBER.NAME , 4))) + " YTD ")) + RIGHT([GPL-Year Period].[GPL-Year Period].CURRENTMEMBER.NAME , 2))) + "]")),FORMAT_STRING='#,##0;(#,##0);0'
SELECT {
[GPL-Data Source].[GPL-Data Source].[Reporting Total]

} DIMENSION PROPERTIES MEMBER_UNIQUE_NAME, MEMBER_NAME, MEMBER_CAPTION, LEVEL_NUMBER, CHILDREN_CARDINALITY ON 0,
HEAD({
[GPL-Value Type].[GPL-Value Type].[MTH],
[GPL-Value Type].[GPL-Value Type].[YTD]
}, 100000) DIMENSION PROPERTIES MEMBER_UNIQUE_NAME, MEMBER_NAME, MEMBER_CAPTION, LEVEL_NUMBER, CHILDREN_CARDINALITY ON 1
... and so on ...
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

I don't believe the issue is the Syntax, as this works beautifully in PAx however - when the view is saved and brought into a PAw workbook - something happens with how PAW translates the MDX, instead replacing ALL dimension elements to .MEMBER and preventing anything being selected, changed, manually changed and saved .. pretty much anything. It becomes a static view on the elements that were saved.

working through this bit by bit - it seems to be when the LEFT statement is applied ... wonder whether there are any recommended workarounds or if there's a fix coming.
many thanks

------------------------------
David Ramos
------------------------------

#PlanningAnalyticswithWatson

Original Message:
Sent: 4/22/2021 8:56:00 AM
From: David Ramos
Subject: Workspace - Dynamic MDX Changes from PAx to PAw

Hi, Just wondering whether this is a Workspace bug or a feature - and whether IBM have plans to address this.

The issue is that I have a limited period field with both discrete and YTD as elements however, I want to show BOTH of them, but controlled from a single filter.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
WITH
MEMBER [GPL-Value Type].[GPL-Value Type].[MTH] AS ([GPL-Value Type].[GPL-Value Type].[MMR Value],STRTOMEMBER((("[GPL-Year Period].[GPL-Year Period].[" + [GPL-Year Period].[GPL-Year Period].CURRENTMEMBER.NAME)) + "]")),FORMAT_STRING='#,##0;(#,##0);0'
MEMBER [GPL-Value Type].[GPL-Value Type].[YTD] AS ([GPL-Value Type].[GPL-Value Type].[MMR Value],STRTOMEMBER((((((("[GPL-Year Period].[GPL-Year Period].[" + LEFT([GPL-Year Period].[GPL-Year Period].CURRENTMEMBER.NAME , 4))) + " YTD ")) + RIGHT([GPL-Year Period].[GPL-Year Period].CURRENTMEMBER.NAME , 2))) + "]")),FORMAT_STRING='#,##0;(#,##0);0'
SELECT {
[GPL-Data Source].[GPL-Data Source].[Reporting Total]

} DIMENSION PROPERTIES MEMBER_UNIQUE_NAME, MEMBER_NAME, MEMBER_CAPTION, LEVEL_NUMBER, CHILDREN_CARDINALITY ON 0,
HEAD({
[GPL-Value Type].[GPL-Value Type].[MTH],
[GPL-Value Type].[GPL-Value Type].[YTD]
}, 100000) DIMENSION PROPERTIES MEMBER_UNIQUE_NAME, MEMBER_NAME, MEMBER_CAPTION, LEVEL_NUMBER, CHILDREN_CARDINALITY ON 1
... and so on ...
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

I don't believe the issue is the Syntax, as this works beautifully in PAx however - when the view is saved and brought into a PAw workbook - something happens with how PAW translates the MDX, instead replacing ALL dimension elements to .MEMBER and preventing anything being selected, changed, manually changed and saved .. pretty much anything. It becomes a static view on the elements that were saved.

working through this bit by bit - it seems to be when the LEFT statement is applied ... wonder whether there are any recommended workarounds or if there's a fix coming.
many thanks

------------------------------
David Ramos
------------------------------

#PlanningAnalyticswithWatson

thanks for the reply Paul,
yeah - because I'm only at the dashboard design end (not data configuration) I'm currently 'stuck' with dimensions that were purely about data .. PA was a distance off when they were set up - so the elements are on the same dimension. I'd thought of using subsets to group the Month and YTD points together - but in order to make that a 'dynamic selector' (whether list or data player) I'd need to apply the subset into the vis. Unfortunately - PA lacks this ability (certainly for now).

The code above therefore was trying to 'capture' parts of the string from a month (eg 2021 03) to bring in the YTD (2021 YTD 03) by using the left/right functions. The part I'm really struggling with however is why this would work well in PAX, but when the same view is in PAW - it can't translate.

On the cross join you mentioned - would you happen to have a syntax example for MDX at all?

thanks again.
David

------------------------------
David Ramos
------------------------------

Original Message:
Sent: Fri April 23, 2021 03:31 PM
From: Paul Simon
Subject: Workspace - Dynamic MDX Changes from PAx to PAw

Hi David

 

I am not entirely sure that I understand what you are trying to do but could you do with a subset that has the Current Period and YTD elements in it, and just cross join these with you value types? We just automatically update subsets like this as part of our month end rollover process.

 

Regards

 

Paul

Original Message:
Sent: 4/22/2021 8:56:00 AM
From: David Ramos
Subject: Workspace - Dynamic MDX Changes from PAx to PAw

Hi, Just wondering whether this is a Workspace bug or a feature - and whether IBM have plans to address this.

The issue is that I have a limited period field with both discrete and YTD as elements however, I want to show BOTH of them, but controlled from a single filter.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
WITH
MEMBER [GPL-Value Type].[GPL-Value Type].[MTH] AS ([GPL-Value Type].[GPL-Value Type].[MMR Value],STRTOMEMBER((("[GPL-Year Period].[GPL-Year Period].[" + [GPL-Year Period].[GPL-Year Period].CURRENTMEMBER.NAME)) + "]")),FORMAT_STRING='#,##0;(#,##0);0'
MEMBER [GPL-Value Type].[GPL-Value Type].[YTD] AS ([GPL-Value Type].[GPL-Value Type].[MMR Value],STRTOMEMBER((((((("[GPL-Year Period].[GPL-Year Period].[" + LEFT([GPL-Year Period].[GPL-Year Period].CURRENTMEMBER.NAME , 4))) + " YTD ")) + RIGHT([GPL-Year Period].[GPL-Year Period].CURRENTMEMBER.NAME , 2))) + "]")),FORMAT_STRING='#,##0;(#,##0);0'
SELECT {
[GPL-Data Source].[GPL-Data Source].[Reporting Total]

} DIMENSION PROPERTIES MEMBER_UNIQUE_NAME, MEMBER_NAME, MEMBER_CAPTION, LEVEL_NUMBER, CHILDREN_CARDINALITY ON 0,
HEAD({
[GPL-Value Type].[GPL-Value Type].[MTH],
[GPL-Value Type].[GPL-Value Type].[YTD]
}, 100000) DIMENSION PROPERTIES MEMBER_UNIQUE_NAME, MEMBER_NAME, MEMBER_CAPTION, LEVEL_NUMBER, CHILDREN_CARDINALITY ON 1
... and so on ...
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

I don't believe the issue is the Syntax, as this works beautifully in PAx however - when the view is saved and brought into a PAw workbook - something happens with how PAW translates the MDX, instead replacing ALL dimension elements to .MEMBER and preventing anything being selected, changed, manually changed and saved .. pretty much anything. It becomes a static view on the elements that were saved.

working through this bit by bit - it seems to be when the LEFT statement is applied ... wonder whether there are any recommended workarounds or if there's a fix coming.
many thanks

------------------------------
David Ramos
------------------------------

#PlanningAnalyticswithWatson