MDX 3+ Months rolling solution required
-
- Posts: 81
- Joined: Tue May 31, 2011 6:29 am
- OLAP Product: TM1 and EV
- Version: 9.1 SP3
- Excel Version: Excel 2010
MDX 3+ Months rolling solution required
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
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
- MVP
- Posts: 1199
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: MDX 3+ Months rolling solution required
Do you have a continuous time dimension or have you split Years and Months/Periods into two dimensions?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 )
-
- Posts: 81
- Joined: Tue May 31, 2011 6:29 am
- OLAP Product: TM1 and EV
- Version: 9.1 SP3
- Excel Version: Excel 2010
Re: MDX 3+ Months rolling solution required
Nope split time dimension otherwise it would be a sinch. Hence the reason why I cannot simply use a lag function....cry....
Cheers
Zark
Cheers
Zark
- gtonkin
- MVP
- Posts: 1199
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: MDX 3+ Months rolling solution required
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.
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.
-
- Community Contributor
- Posts: 219
- Joined: Mon Jul 23, 2012 8:31 am
- OLAP Product: TM1
- Version: 10.2.2
- Excel Version: 2010
Re: MDX 3+ Months rolling solution required
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?
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?
-
- Site Admin
- Posts: 1454
- Joined: Wed May 28, 2008 9:09 am
Re: MDX 3+ Months rolling solution required
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
- MVP
- Posts: 808
- Joined: Sat Sep 03, 2011 11:10 pm
- OLAP Product: TM1
- Version: PA 2.0.5
- Excel Version: 2016
- Contact:
Re: MDX 3+ Months rolling solution required
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
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
-
- Community Contributor
- Posts: 219
- Joined: Mon Jul 23, 2012 8:31 am
- OLAP Product: TM1
- Version: 10.2.2
- Excel Version: 2010
Re: MDX 3+ Months rolling solution required
Only meant to say not elegant as in sticking to a solution with 2Dim per the OP set up.David Usherwood wrote:I'd take issue with the statement that using a flat time dimension for reference is inelegant.
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?
-
- MVP
- Posts: 2832
- Joined: Tue Feb 16, 2010 2:39 pm
- OLAP Product: TM1, Palo
- Version: Beginning of time thru 10.2
- Excel Version: 2003-2007-2010-2013
- Location: Atlanta, GA
- Contact:
Re: MDX 3+ Months rolling solution required
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
- MVP
- Posts: 1199
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: MDX 3+ Months rolling solution required
@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.
-
- MVP
- Posts: 2832
- Joined: Tue Feb 16, 2010 2:39 pm
- OLAP Product: TM1, Palo
- Version: Beginning of time thru 10.2
- Excel Version: 2003-2007-2010-2013
- Location: Atlanta, GA
- Contact:
Re: MDX 3+ Months rolling solution required
Perhaps you didn't read this:gtonkin wrote:@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.
I posted a non-MDX rule-based solution.tomok wrote:It should be pretty obvious you can't use MDX for this when you have a split time dimension.
- gtonkin
- MVP
- Posts: 1199
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: MDX 3+ Months rolling solution required
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.
-
- MVP
- Posts: 2832
- Joined: Tue Feb 16, 2010 2:39 pm
- OLAP Product: TM1, Palo
- Version: Beginning of time thru 10.2
- Excel Version: 2003-2007-2010-2013
- Location: Atlanta, GA
- Contact:
Re: MDX 3+ Months rolling solution required
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:gtonkin wrote: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.
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);
-
- Posts: 81
- Joined: Tue May 31, 2011 6:29 am
- OLAP Product: TM1 and EV
- Version: 9.1 SP3
- Excel Version: Excel 2010
Re: MDX 3+ Months rolling solution required
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
Thankyou for your input.
Cheers
Zark
- PavoGa
- MVP
- Posts: 617
- Joined: Thu Apr 18, 2013 6:59 pm
- OLAP Product: TM1
- Version: 10.2.2 FP7, PA2.0.9.1
- Excel Version: 2013 PAW
- Location: Charleston, Tennessee
Re: MDX 3+ Months rolling solution required
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?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 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)
)
Ty
Cleveland, TN
Cleveland, TN
-
- MVP
- Posts: 2832
- Joined: Tue Feb 16, 2010 2:39 pm
- OLAP Product: TM1, Palo
- Version: Beginning of time thru 10.2
- Excel Version: 2003-2007-2010-2013
- Location: Atlanta, GA
- Contact:
Re: MDX 3+ Months rolling solution required
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.PavoGa wrote: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?