I'm trying to do a filter that uses {[Period].[Months].Members} as the
set that will be filtered and uses as the condition of the filter a
reference to the current member of the Period dimension, outside the
set of the filter. To resolve it I've tried to use an alias set, as in
the following expression:

Filter ({[Period].[Months].Members} AS FilterSet,
(FilterSet.CurrentMember,[Schedule].[Amount]) > Rank
([Period].CurrentMember, {[Period].[Months].Members}) – Rank
(FilterSet.CurrentMember, {[Period].[Months].Members})

I want to filter the member of the Period dimension where the tuple
([Period], [Schedule].[Amount]) is more than the distance between the
Period member of the filter set and the current member of Period
dimension (outside the filter set). But, despite that the expression's
syntax is correct, the expression:

Rank ([Period].CurrentMember, {[Period].[Months].Members}) – Rank
(FilterSet.CurrentMember, {[Period].[Months].Members})

Always returns zero, so the AS don't understand that the
[Period].CurrentMember is referencing the external member of the
Period dimension and not the member of the filter set.

Does anybody can help me??

By the way, the formula that I'm trying is simple but a quite complex
to explain, take a look at the following example:

Assume that the values between the vertical bars mean:

Jan Feb Mar Apr
So, the Monthly Cost 200 of Jan must be added to the result of the
formula in Jan, Feb and Mar, because the Amount of Payments is 3. In
the same way, the Monthly Cost 100 of Feb must be added to the result
formula in Feb, Mar, Apr and May. Therefore, the result of the formula
for the example above should be:

Jan Feb Mar Apr May Jun
200 300 800 900 400 300

And the entire expression that I've tried to calculate this is:

Sum( Filter ({[Period].[Months].Members} AS FilterSet,
(FilterSet.CurrentMember,[Schedule].[Amount of Payments]) >
Rank([Perid].CurrentMember, {[Period].[Months].Members}) - Rank
(FilterSet.CurrentMember, {[Period].[Months].Members})
),
[Schedule].[Monthly Cost])

But it doesn't work because inside a filter condition, for the reason
that the filter set is the [Period].[Months] members, all references
to the CurrentMember of the Period dimension will be understood as the
CurrentMember member of the filter set. One solution could by use also
like a variable, setting its values before enter in the filter
expression, but I don't if its possible.

Based on the sample data, there are a couple of issues:

- The Alias Set is not saving the [Period] context

- [Monthly Cost] of future months is added into prior


This MDX expression should address those 2 issues:

Sum(Filter([Period].[Months].Members,
(Period.CurrentMember,[Schedule].[Amount of Payments]) >
Rank([PeriodSet].Item(0),[Period].[Months].Members)
- Rank(Period.CurrentMember,[Period].[Months].Members)
And Rank([PeriodSet].Item(0),[Period].[Months].Members)

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Yes, actually my problem was with the first issue that you wrote and
your solution resolved it, the second one I knew about it but I was
trying to resolve the first before.

Very interesting this way to "emulate" a variable before the filter,
creating a new Sum with one member, outside the filter, just to AS
understand exactly what PeriodSet refers to.

About the second issue, I prefered to limit the set of the filter to
the months before the current month, take a look in the formula that I
used:

Sum({[Period].CurrentMember} as PeriodSet,
Sum(Filter({([Period].[Months].Members.Item(0):[Período].CurrentMember)},
([Schedule].[Amount of Payments])>
(Rank(PeriodSet.Item(0),{[Period].[Months].Members}) -
Rank([Period].CurrentMember,
{[Period].[Months].Members}))
),
[Schedule].[Monthly Cost]))

Thank you,

Vinicius Bellino



Deepak Puri <deepak_puri (AT) progressive (DOT) com> wrote

You're right, limiting the set to prior months is simpler than adding
that condition in the filter. If the [Period] dimension has an [All]
level, then this is equivalent to:

PeriodsToDate([Period].[All])


- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!