MDX help

[conray]

I have 3 dimensions, year (2012, 2013), month (01, 02, 03, 04) and period (201303, 201304).
I am trying to create a dynamic subset on period such that when user selects Year and Month, it will retrieve the respective periods for that fiscal year.
Fiscal period begins from April to next year March (FY2012 - 201204 ~ 201303).

I also have a "Fiscal Year" attribute in period dimension like this:
Period Fiscal Year
201303 2012
201304 2013

I know that i create the dynamic subset referencing to 1 other dimension but is it possible to do it referencing 2 dimension?

1 dimension example (result is wrong because if user selects year 2013 month 02, it shows all periods of FY2013 which is not what i want)
{TM1FILTERBYLEVEL( {FILTER( {TM1SubsetAll( [cdwPeriod] )}, [cdwPeriod].[Fiscal Year] = [cdwYear].[Fiscal Year])}, 0)}

[rozef]

Hi conray,

unfortunatly it is impossible to reference a set of an antoher dimension in a (TM1) MDX subset.
You can compare different element with static reference but it won't help you for any view.

If you won't some asynchronous reports I heard it is possible to make MDX views in Perpectives (but I don't know how).
You can also use a simple excel formula in one of your dimension.

[conray]

Do you think it is possible for this to work?

{FILTER( {TM1SubsetAll( [cdwPeriod] )}, [cdwPeriod].[Fiscal Year] = ([cdwYear].[Fiscal Year] - [cdwMonth].[PriorYearComponent]))}

Selected Year = 2013
Selected Month = Feb

Fiscal Year Attribute for 2013 = 2013
PriorYearComponent Attribute for Feb = 1

So in this case, Jan, Feb and Mar will have value 1 for PriorYearComponent.

Anyone knows if it is possible to perform subtraction on attributes in MDX?

[conray]

just did the test, it works.
previously it didnt work because my attribute type is Text.
created another attribute with type numeric and use the same MDX statement, result: its working.

[rozef]

I am very surprised if it works.
Maybe I am wrong but in my comprehension
[cdwYear].[Fiscal Year] and [cdwMonth].[PriorYearComponent]
will always refer to first element of "cdwYear" and "cdwMonth" dimensions (index 0) not to the assiociated elements in your view / active form.
Did you try it for other Year/month?

[conray]

yep i tried with all possible year/month combination, it works.

i guess i did not mention that year dimension and month dimension is actually in the title of the view, does it make sense now?

[lotsaram]

This context sensitivity of MDX dynamic subsets to the selected title elements in other dimensions is a really nice feature. However as it's undocumented there could be a risk of it being broken in some future release.