TM1 Forum

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,

What expression do you already have ?

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

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.

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

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.
HTH