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?
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.
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]
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:
#
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.
That worked!!!
Great
Thanks