Dimensional:Filter members from multiple hierarchies

[Sam2535]

I have 2 different Hierarchies in the time Dimension 1: years->Months 2nd: Quarters->months. now I have to create YTD and QTD in a crosstab based on user selection of a specific month.
The Problem is I cannot travel using dimensional functions from one member of a Hierarchy to another member of a different Hierarchy. For example: the user selects Nov-18 month from prompt based off of 2nd hierarchy (Quarters->Months). Using that month I can't use Periodstodate() function to figure out YTD cos there is not years level in 2nd Hierarchy

So I used the Caption to extract the text and then match it with text of month level in years->Months hierarchy to get to the correct month

filter([Revenue_Model].[Date].[Date].[Month],caption([Revenue_Model].[Date].[Date].[Month]) in (
caption([Revenue_Model].[Date].[By Quarter].[Month]->?p_Month?)
))

from here when i use the above data item in Periodstodate() function i get the "Invalid Coercion from MemberSet to Member" error

what should i do from here OR is the completely different approach to filter accross Hierarchies?

[CognosPaul]

I guess the first question should be WHY doesn't the quarter hierarchy have year in it? If the user can select Nov-18, that means the year is already encoded in the data. Adding year to the hierarchy shouldn't affect anything.

Can you post an example of the memberuniquenames from the year-month hierarchy and the quarter-month hierarchy? You might be able to use macro functions to programmatically switch the hierarchy from what the user selects.

[Sam2535]

changing the cube is not an option in my case

Nov-18 MUN from Year Month Hierarchy     :[Revenue Model].[Date].[Date].[Month]->:[PC].[@MEMBER].[20181101-20181130]
Nov-18 MUN from Quarter Month Hierarchy:[Revenue Model].[Date].[By Quarter].[Month]->:[PC].[@MEMBER].[20181101-20181130]

[CognosPaul]

So the user may select [Revenue Model].[Date].[By Quarter].[Month]->:[PC].[@MEMBER].[20181101-20181130] from the prompt.

Try using the following macro to switch the selected mun to the Year Month hierarchy:

Code Select Expand

# 
substitute('By Quarter','Date',
prompt('Month','mun','','','[Revenue Model].[Date].[By Quarter].[Month]')
)
#


You can wrap that in a periodsToDate function to get the correct set you need.

[Sam2535]

That worked!!!
Great
Thanks