Hi All
I have a tm1 cube as a datasource for a report.
One of the dimensions, "Time" contains months and YTD members. Each of the month elements has an attribute called "YearToDate" where it's corresponding year to date member is stored, so for example, the member "Jun" has within the "YearToDate" attribute, the value "Jun YTD".
The issue I am facing relates to a crosstab I am building containing some simple data on expenses. I am required to deliver two columns of data, one for the month and one for the year to date.
I've set up a value prompt which allows the user to pick the month required, but what I am struggling with is to create a data item to drag into my crosstab which correctly looks at the attribute "YearToDate" and brings back the "Jun YTD" from the attribute held against the parameter that the user has selected - "Jun". The column for "Jun" works fine, just the "Jun YTD" is not playing ball :(
How do I go about achieving this ?
Thanks
Ajay
TM1 10.2.2 and BI 10.2.2
Hi,
Is their a YTD member or hierarchy? You can get the memberCaption of the month and filter ytd members using the filter function like filter(ytd heirarchy, roleValue('_memberCaption'(ytd hierarchy)) = [Data Item you created for the memberCaption of the current mont]),0). If you have YTD ammended to teh month name in the ytd hierarchy then add YTD using concat to the member Caption of the month.
Example:
Month Caption = June 2017
YTD month Caption = June 2017 YTD
Good luck
New guy
Thanks New_Guy,
I actually found another way which worked nicely and that was to use the "Cousin" function:
cousin (
[ProfitandLoss].[Time].[Time].[level002]->?Parameter_Period?,
[ProfitandLoss].[Time].[Time].[level001]->:[TM].[Time].[Time].[@MEMBER].[YTDs]
)
The structure of the date hierarchies in the dimension enabled this to work since all yeartodate members sat under the main consolidation called "YTDs", and were direct cousins to the level002 derived parameter.
Thanks once again for your help.
Ajay