MDX 3+ Months rolling solution required

[7Zark7]

Ok completely lost on this one:

I am trying to build an mdx statement that will return the last 3 months of elements based on grabbing the "CurrentMonth" value from our system attributes cube. The trick however is is that we are dealing with financial years so using a lag function will not work unless using some sort of iif funtion ( could not get this to work ) ?. As part of our solution we have a month attribute cube that hold "PreviousMonths". There are various solutions that I can use, like building rolling subsets and rolling hierarchies etc, however I would like to skip this and see whether or not it is possible via some mdx solution. I have tried the following with success however getting a 3 monthly rolling is troublesome:

###############
Get Prior Month
################

{Filter( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Month] )}, 0)}
,[Month].currentmember.Properties("NextMonth")
= [System Attributes].( [System Measure].[Current Month], [System DataType].[String] )
)}


################
Get Current and Prior Month
################

{
{StrToMember("[Month].["+ [System Attributes].( [System Measure].[Current Month], [System DataType].[String] ) +"]")}
, {Filter( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Month] )}, 0)}
,[Month].currentmember.Properties("NextMonth")
= [System Attributes].( [System Measure].[Current Month], [System DataType].[String] )
)}
}


I have tried various combinations , however 3 month rolling will not work.

Any ideas??

Cheers

Zark

[gtonkin]

The trick however is is that we are dealing with financial years so using a lag function will not work unless using some sort of iif funtion ( could not get this to work )

Do you have a continuous time dimension or have you split Years and Months/Periods into two dimensions?

[7Zark7]

Nope split time dimension otherwise it would be a sinch. Hence the reason why I cannot simply use a lag function....cry....

Cheers

Zark

[gtonkin]

Not sure you will get this right as months that span years will be the issue.
I have generally added a Rolling Average element to my source dimension and calculated using a formula that looks at last year YTD, subtracts the YTD months before my period and then adds this year YTD e.g. If I am in Jan, I need Nov, Dec and Jan
Dec YTD PY - Oct YTD PY + JAN YTD CY
Will obviously require a bit of tweaking to get working on your side - I generally use a 13 month rolling average.

[RJ!]

Just throwing it out there...

Why not create a Dim that has the consecutive periods, run your MDX against that to get your "last 3mths" and then chop the "Period" back to Month & Year?

Not a particularly elegant solution but would solve for what you want?

[David Usherwood]

I'd take issue with the statement that using a flat time dimension for reference is inelegant. We use this quite regularly for multi-period lags and leads, eg for insurance modelling, and it works well. Set it up once, populate for (say) 30 years, and use DIMIX/DIMNM for offsets. Don't store any data against it, only attributes.

[paulsimon]

Hi

Without wishing to start the one vs two dimensions for time argument again, this is definitely a case where it would be a lot easier with a single year-month time dimension. With that I wouldn't bother with MDX. It would be better and much faster to create 3 month rolling hierarchies.

With the situation you have, I would look at adding a measure and using rules to calculate this. It will probably be easier than the MDX approach, and won't add much to cube size if you only want a rolling 3 months to the current period.

Regards

Paul

[RJ!]

I'd take issue with the statement that using a flat time dimension for reference is inelegant.

Only meant to say not elegant as in sticking to a solution with 2Dim per the OP set up.

Another solution might be to add 3 (or 6) attributes to the Month Dim to store Mth-1, Mth-2, Mth-3 & then some logic to -1 the year when you get to Jan, Feb & Mar?

[tomok]

It should be pretty obvious you can't use MDX for this when you have a split time dimension. It's one of the downsides to splitting. IMO, the easiest and most maintenance free solution will be to add three attributes to the month dimension: PreviousMonth_1, PreviousMonth_2, PreviousMonth_3, and three attributes to the year dimension: PreviousYear_1, PreviousYear_2, PreviousYear_3. For example, in February of 2016 these attributes would be populated with January, December, November, 2016, 2015 and 2015, respectively. Then add a new measure and call it "3 Month Rolling". You can then have this rule to populate:

Code: Select all

['3 Month Rolling'] = N:
DB(ThisCube, Dim1, Dim2, Dim3....ATTRS('Month', !Month, 'PreviousMonth_1'), ATTRS('Year', !Year, 'PreviousYear_1) +
DB(ThisCube, Dim1, Dim2, Dim3....ATTRS('Month', !Month, 'PreviousMonth_2'), ATTRS('Year', !Year, 'PreviousYear_2) +
DB(ThisCube, Dim1, Dim2, Dim3....ATTRS('Month', !Month, 'PreviousMonth_3'), ATTRS('Year', !Year, 'PreviousYear_3);

[gtonkin]

@Tomok - just pointing out that I cannot see your MDX working as the Year should only be derived for PreviousYear_1 - and then only when the Month is Jan or Feb (Period 01 or 02) - not sure myself how to put the conditionals into MDX though.

[tomok]

@Tomok - just pointing out that I cannot see your MDX working as the Year should only be derived for PreviousYear_1 - and then only when the Month is Jan or Feb (Period 01 or 02) - not sure myself how to put the conditionals into MDX though.

Perhaps you didn't read this:

It should be pretty obvious you can't use MDX for this when you have a split time dimension.

I posted a non-MDX rule-based solution.

[gtonkin]

Apologies Tom, polarised and mistyped - the Rule won't work - Still would need to include an IF in the Rule to check if we reference this year or last year.

[tomok]

Apologies Tom, polarised and mistyped - the Rule won't work - Still would need to include an IF in the Rule to check if we reference this year or last year.

Yes, sorry, you would have to create your own attribute cube that has both the month and year as dimensions and store your lookup values there. The rule would then be:

Code: Select all

['3 Month Rolling'] = N:
DB(ThisCube, Dim1, Dim2, Dim3....DB('LookupCube', !Month, !Year, 'PreviousMonth_1'), DB('LookupCube', !Month, !Year, 'PreviousYear_1) +
DB(ThisCube, Dim1, Dim2, Dim3....DB('LookupCube', !Month, !Year, 'PreviousMonth_2'), DB('LookupCube', !Month, !Year, 'PreviousYear_2) +
DB(ThisCube, Dim1, Dim2, Dim3....DB('LookupCube', !Month, !Year, 'PreviousMonth_3'), DB('LookupCube', !Month, !Year, 'PreviousYear_3);

[7Zark7]

Thanks for the info guys. Every solution here I have toyed with, but stubborn me wanted to see whether I could derive it referencing a cell string value stored in our global cube using .properties method to grab prior month attributes values to derive it. It was more a challenge to see if it could technically be done deriving it the way I have.

Thankyou for your input.

Cheers

Zark

[PavoGa]

Thanks for the info guys. Every solution here I have toyed with, but stubborn me wanted to see whether I could derive it referencing a cell string value stored in our global cube using .properties method to grab prior month attributes values to derive it. It was more a challenge to see if it could technically be done deriving it the way I have.

If I understand correctly, the core issue is you need to know how to incorporate a string value stored in a cube as a member or set in an MDX statement? Is that correct?

If so, here are a couple of MDX snippets I use to return the current version a user is working with to default their views to that particular version:

Code: Select all

# return a member of Versions using TM1Member on the cube intersection in the clients attributes.
{TM1Member(
	TM1SUBSETALL([Versions]).ITEM([}ElementAttributes_}Clients].(StrToMember("[}Clients].["+USERNAME+"]"), [}ElementAttributes_}Clients].[Default Version DIMIX])-1)
	,0)}

# Subset for Versions based on Client default Version.
UNION(
	{TM1Member(
		TM1SUBSETALL([Versions]).ITEM([}ElementAttributes_}Clients].(StrToMember("[}Clients].["+USERNAME+"]"), [}ElementAttributes_}Clients].[Default Version DIMIX])-1)
		,0)}
	, TM1Sort(
		TM1FilterByLevel(
			TM1SUBSETALL( [Versions] )
			, 0)
		, ASC)
	)

It looks like the key difference is the use of the TM1Member function. Hope this helps.

[tomok]

If I understand correctly, the core issue is you need to know how to incorporate a string value stored in a cube as a member or set in an MDX statement? Is that correct?

I'm pretty sure that is a side issue. The core issue was the OP trying to create an MDX-based solution that would allow him to have a rolling three-month history when having a split time dimension with years and months in separate dimensions. No amount of MDX is going to get you there.