calculated member

Hi,

I created the calculated member ( mkdn dollars/ sale dollars) in the cube but when I see the variance(Prior year Chg), it seems it calculates the mkdn dollars - prior year chg / sales dollars -prior year chg

Here are samples for better understanding:

==================================================================

1st week - 2008

current period prior period prior period chg

-

Mkdn % 48.3 % 46.99% 79.82 %

mkdn dollars 524,725 490, 198 34,526

sales dollars 1086,433 1043,178 43,254

-

-- Prior Year Chg

-

([Time Calculations].[Prior Year Chg]=

[Time Calculations].&[Current Period] - [Time Calculations].[Prior Year]);

==================================================================

If I want to get prior priod chg ( 48.3 -46.99) based on mkdn % then I should I change the prior year chg calc ?

Also, I tried to create named calcuation in DSV for mkdn % but it returns a wrong value. (why does not return a right

value? something missing?) This is the expression I created as under:

==================================================================

CASE
WHEN [Net_Sale_Dollars] = 0 THEN NULL
ELSE [POS_Mrkdn_Dollars]/[Net_Sale_Dollars]
END

==================================================================

Please give me any comments

Thank you in advance.

You already tried to use the function ParalellPeriod and PeriodsToDate?

regards!

|||

Check the order of calculations in the cube MDX script, and make sure that [Mkdn %] precedes [Prior Year Chg], so that [Mkdn %] gets computed prior to [Prior Year Chg].

"Also, I tried to create named calcuation in DSV for mkdn %" - but what measure aggregation function are you applying to this, since it is a ratio?

|||

If I put mkdn % in the measure scope then it gives an error as follow:

--

Error 1 A set has been encountered that cannot contain calculated members. 0 0

--

[mkdn %-] calculation is as follows:

CREATE MEMBER CURRENTCUBE.[MEASURES].[ Mkdn %]

AS IIF ([Measures].[Net Sale Dollars] = 0, NUll,

[Measures].[POS Mrkdn Dollars]/[Measures].[Net Sale Dollars]),

FORMAT_STRING = "Percent",

VISIBLE = 1 ;

calculated member can't be contained in time calculation?

And you are right, since this measure is ratio I can't you any aggregate function. ( I'm so stupid )

Please give me any comments.

Thanks.

|||"Error 1 A set has been encountered that cannot contain calculated members" - if you received this error when dropping the time calculation in the top (subcube) filter in the BIDS cube browser, try placing it in the bottom (where) filter section instead, and see if you still get the error.|||

This error pops up when I put ' mkdn % ' calc measure in the mdx scripts (scope statement which shows all measures )

I also tried to drop the time calc in bottom filter section but it didn't work.

my question is calculated member can't be contained in measure scope in mdx script?

(it doesn't make sense to me)

What else do I need to try to correct this? Please give me a direction.

Thanks always Deepak

|||That's a different issue - calculated member definitions have global scope, so they shouldn't be placed inside scoped calculations; but subsequent assignments to such members can be scoped.|||

Okay. you mean calculated member definitions have global scope, these measrues can't be contained in scoped calculation.

If so, to calculate the varience for calculated members, do i have to write mdx scripts instead of using Time calculation which calculate prior year, variance, variance % and so on?

And this is the only way to calculate this scenario? or is there any way to use time calculation(shell dimension) for the calculated measures?

|||"If so, to calculate the varience for calculated members, do i have to write mdx scripts instead of using Time calculation which calculate prior year, variance, variance % and so on?" - could you explain what you mean in more detail? For example, the calculations generated by the Time Intelligence Wizard are added to the cube MDX script, so I'm not sure what distinction you're drawing between time calculations and MDX scripts. Typically a calculated member will be created (Create Member ..), and then scoped assignment(s) will be used to apply calculations to the appropriate subcube(s) containing the member.|||

my mdx script is exactly same as you described.

And I manually created time calc as under:

/* calculated member */

create member prior year

create member prior year chg

create member mdkn %

AS IIF ([Measures].[Net Sale Dollars] = 0, NUll,

[Measures].[POS Mrkdn Dollars]/[Measures].[Net Sale Dollars]),

FORMAT_STRING = "Percent",

VISIBLE = 1 ;

.....

/* scope for specific measures */

>> when I added calculated member ( mkdn %) then it gave an error and as you said since calc members have global scope, I can't add calc member in scoped calc.

scope {

[measures].[sale dollars]

....

}

scope( [time].[calendar year].[Calendar Year].members,[Time].[TimeKey].members);

-- prior year calc
([Time Calculations].[Prior Year]=
(ParallelPeriod([Time].[Calendar Hierarchy].[Year], 1,
[Time].[Calendar Hierarchy].currentmember)
,[Time Calculations].[Current Period])
);

end scope;

-- Prior Year Chg :This calc does not depend on specific hierarchy

([Time Calculations].[Prior Year Chg]=
[Time Calculations].[Current Period] - [Time Calculations].[Prior Year]);

end scope;

-

This time calc works fine except all calculated members (current period and prior period work properly but prior year chg doesn't ; i.e. [mkdn % - prior year chg] computes each measure in current period and prior period and then recalculate the calcuated members as I explained at first. )

Hope you can get better understanding and please give me some comments.

Thanks.

|||

I was referring to calculated member creation, not to assignment - so see if you can repeat the scoped assignment separately for [mdkn %] alone, to resolve the error, like:

Code Snippet

/* scope for [mdkn %] measure */

scope([measures].[mdkn %]);

scope( [time].[calendar year].[Calendar Year].members,[Time].[TimeKey].members);

-- prior year calc
([Time Calculations].[Prior Year]=
(ParallelPeriod([Time].[Calendar Hierarchy].[Year], 1,
[Time].[Calendar Hierarchy].currentmember)
,[Time Calculations].[Current Period])
);

end scope;

-- Prior Year Chg :This calc does not depend on specific hierarchy

([Time Calculations].[Prior Year Chg]=
[Time Calculations].[Current Period] - [Time Calculations].[Prior Year]);

end scope;

|||

I put (mkdn % + all calc members) scope seperately outside the time calc sope, and repeated time calc scripts( prior year and prior year chg) with calculated members, then it works

Thanks a lot !!!!

You Save me