TM1 MDX

Post Reply
dharav9
Posts: 54
Joined: Wed Aug 15, 2018 3:18 pm
OLAP Product: TM1
Version: 10.3
Excel Version: 2016

TM1 MDX

Post by dharav9 » Fri May 15, 2020 1:59 pm

Hi, All

We have a lookup cube where Fcst Value = FY2021 has mentioned.

We have a dynamic subset called FcstYr which retrieves the vaelue based on lookup cube;
MDX of this subset is:

{
FILTER(
TM1SUBSETALL( [Time]),
[Time].CurrentMember.Name = [Globals].([Globals].[Fcst Year], [Value_Msr].[ValueS])
)
}

I would like to extract past 3 years dynamically based on current fcst year. E.g.: FY2018, FY2019, FY2020 based on this.

When I tried following result came FY2022.

{
FILTER(
TM1SUBSETALL( [Time]),
[Time].CurrentMember.prevmember.Name = [Globals].([Globals].[Fcst Year], [Value_Msr].[ValueS])
)
}

I couldn't able to use Lastperiods function as well.

Can anyone assist?

Thank You
Dharav

Wim Gielis
MVP
Posts: 2426
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: TM1 MDX

Post by Wim Gielis » Fri May 15, 2020 2:17 pm

Without seeing the dimension elements and the structures, it's difficult to comment. Certainly if the MDX for LastPeriods is not given.

Should we guess what you see in front of you ?

Anyway, this should work and serves as an example - a straight copy from my website by the way:

Code: Select all

{LastPeriods( 4, StrToMember("[Year].[" + [System].([System_Prm].[Year],[System_Msr].[Text]) + "]") )}
dharav9 wrote:
Fri May 15, 2020 1:59 pm
I couldn't able to use ...
You are missing some words here.

Can you also find a better topic title ? TM1 is pretty much useless in the title, given the forum, and MDX is very vague too. Thanks.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Selecting elements in the Subset Editor quickly

Post Reply