Problem in 'Rolling 12 Paid Months' member calculation

# Problem in 'Rolling 12 Paid Months' member calculation

• 2012年1月9日 8:20

I have a calculated measure like: Measure C = MAX(Measure A) / Distinct count(B).

I need to create a calculated member called 'Rolling 12 Paid Months' which is created as :

CREATE MEMBER CURRENTCUBE.[Reported Paid Date].[Paid Date].[Rolling 12 Paid Months] as AGGREGATE(

STRTOMEMBER( "[Reported Paid Date].[Paid Date].[Paid Months].[" +

[Reported Paid Date].[Cycleend].children.item(0).properties("key") +"]")  :

STRTOMEMBER( "[Reported Paid Date].[Paid Date].[Paid Months].["  +

[Reported Paid Date].[Cycleend].children.item(0).properties("key") +"]" ) .lag(11)

)

The problem here is that when browsing the member 'Rolling 12 Paid Months' in rows and measure 'Measure C' in columns, it is not aggregating the value. Instead, the monthly calculated results are summed up giving a large value.

Please provide me a solution in this. Do we need to modify the MDX of how the calculated member 'Rolling 12 Paid Months' is calculated?

### 全部回复

• 2012年1月13日 2:05
版主

Hi Anish,

Yes, you need to modify the MDX. I don't understand the part of "Distinct count(B)", so below mdx only includes MAX(MeasureA). I think it will be easy for you to correct it by including the "Distinct Count(B)" logic -

CREATE MEMBER CURRENTCUBE.[Reported Paid Date].[Paid Date].[Rolling 12 Paid Months] as

MAX(

{STRTOMEMBER( "[Reported Paid Date].[Paid Date].[Paid Months].[" +

[Reported Paid Date].[Cycleend].children.item(0).properties("key") +"]")  :

STRTOMEMBER( "[Reported Paid Date].[Paid Date].[Paid Months].["  +

[Reported Paid Date].[Cycleend].children.item(0).properties("key") +"]" ) .lag(11)
},
MeasureA
)

the bold parts are different from your MDX.

Please let me know if you have more questions.

Regards,
Jerry

• 2012年3月29日 20:11

Hey!!!!

I found the solution

Measure C = MAX(Measure A) / Distinct count(B).

CREATE MEMBER CURRENTCUBE.[Measures].[Member Age]
AS SUM([MemberForAvgAge].[Member Id Int].[Member Id Int].MEMBERS, [Measures].[Max Member Age]),
VISIBLE = 0 ;

CREATE MEMBER CURRENTCUBE.[Measures].[Average Age]
AS IIF([MEASURES].[Member Count] = NULL,
NULL,
[MEASURES].[Member Age]/[MEASURES].[Member Count]
),
FORMAT_STRING = '#,##0.0',
VISIBLE = 1 ,  DISPLAY_FOLDER = 'Enrollment';

Sorry i donot have much time to translate the code in generic format. But this logic works!!!

Calculating the Max measure as a simple measure and scoping it in the calculated section is quite different from making the max measure calculated one in the calculated section. This is because all the calculated values are determined in the calculated section during the query run time.

Thanks,

Anish