TM1 MDX

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

TM1 MDX

Post by dharav9 »

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: 3182
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: TM1 MDX

Post by Wim Gielis »

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 pmI 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

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Post Reply