Mdx to get 2 years date

Post Reply
manu0521
Posts: 124
Joined: Wed Nov 26, 2014 8:32 pm
OLAP Product: IBM TM1, Planning Analytics
Version: PA 2.0.5
Excel Version: 2016

Mdx to get 2 years date

Post by manu0521 » Wed Jun 09, 2021 10:43 pm

HI ,

I wanted to extract a flat file from tm1 on a daily basis that would extract 2 years of data. I have to create a subset dynamically that would give the n level for 2 years of date.

My time dimension is in this format :

Year -2021
Month - Jun 2021
Date - 20210609

So i want a subset having all values from Jun 2019 to June 2021 , meaning take the current month and go back 23 months and give all dates.

Any experts on mdx on how to do this with today() in mdx .

Thanks,

Wim Gielis
MVP
Posts: 2711
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.8
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Mdx to get 2 years date

Post by Wim Gielis » Wed Jun 09, 2021 10:47 pm

What expression do you already have ?
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 117 TM1 articles and a lot of custom code
Newest blog article: Avoid circular references https://github.com/wimgielis

manu0521
Posts: 124
Joined: Wed Nov 26, 2014 8:32 pm
OLAP Product: IBM TM1, Planning Analytics
Version: PA 2.0.5
Excel Version: 2016

Re: Mdx to get 2 years date

Post by manu0521 » Thu Jun 10, 2021 12:52 am

I have a month number alias on months element. so for example,

for Jun 2021 my alias is 202112 (fiscalyearno)

I can get the currnt months alias which is 202112 then get the year part and subtract with 2.

SO now i would form 201912 - which will be jun 2019 .

Now I need everymonths from Jun 2019 to Jun 2021 and then do drill down level on dates for this months.

Is there a greater than and less than in mds ? so that i can do >=201912 and <=202112

{TM1FILTERBYLEVEL({TM1DRILLDOWNMEMBER({[Timeperiod].[201912],[TimePeriod].[202001]},ALL,RECURSIVE)}, 0)}

I can also get the index and subtract the index 24 times, but i am not sure that is the right way to get my 24 months in the mdx

Wim Gielis
MVP
Posts: 2711
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.8
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Mdx to get 2 years date

Post by Wim Gielis » Thu Jun 10, 2021 6:17 am

I would calculate the first day in a variable in TI and also the last day.

Then you can find inspiration here: viewtopic.php?t=15025
Insert your calculated variables for the threshold and do an AND in the filter since you must filter >= begin date and <= last day.

To calculate period offsets like -24 periods I would use a lookup cube with a month dimension and a dimension for offsets like 1, 2, … but also -1, …, -24, …. With a few simple measures (year and month) you are able to populate the entire cube with hard values and do a CellGetS.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 117 TM1 articles and a lot of custom code
Newest blog article: Avoid circular references https://github.com/wimgielis

Mark RMBC
Community Contributor
Posts: 247
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: Mdx to get 2 years date

Post by Mark RMBC » Thu Jun 10, 2021 10:03 am

Hi,

If you held these start and end timeperiods in say an Admin cube, then something like the following mdx should work (I assume an admin cube with 2 dimensions),

FILTER({TM1FILTERBYLEVEL({TM1SUBSETALL([Timeperiod])}, 0)}, [Timeperiod].Currentmember.Properties('fiscalyearno') >= [Admin].([Versions].[Actual],[Measure].[StartTimePeriod]) and [Timeperiod].Currentmember.Properties('fiscalyearno') <= [Admin].([Versions].[Actual],[Measure].[EndTimePeriod]))

Edit: The above can actually be simplified as follows:

FILTER({TM1FILTERBYLEVEL({TM1SUBSETALL([Timeperiod])}, 0)}, [Timeperiod].[fiscalyearno] >= [Admin].([Versions].[Actual],[Measure].[StartTimePeriod]) and [Timeperiod].[fiscalyearno] <= [Admin].([Versions].[Actual],[Measure].[EndTimePeriod]))


But of course the devil is in the detail!

So June is period 12, I presume July 2019 would equal 202001? - find that a bit strange but anyway.

regards,

Mark
Last edited by Mark RMBC on Fri Jun 11, 2021 7:39 am, edited 1 time in total.

User avatar
gtonkin
MVP
Posts: 924
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.9.4; PAoC 2.0.9.3
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: Mdx to get 2 years date

Post by gtonkin » Thu Jun 10, 2021 6:05 pm

I'll throw another option into the mix - based on a Daily period dimension but concepts should still apply.

I have a subset that is updated via TI with the current business day each night.
The Period in the subset is used as the basis from which to derive the last 32 days.
The StringToMember bit with the LEFT() is used when I need MTD or YTD periods returned in similar subsets.

Code: Select all

TM1SORT(
{GENERATE(
{TAIL(
{LASTPERIODS(32,
TM1Member([Period].[_S-Current Period].|tem(0),0)
) },
32)},
{StrToMember("[Period].[" + LEFT([Period].CurrentMember.Name,10) + "]")}
)},DESC)
HTH

Post Reply