year to year-1 comparison

[Kurt Hofman]

I'm trying to include a year to year-1 comparison (growth/shrink) in a pivot and I'm getting stuck here.

I testing with the Holefood-sample

Current MDX : SELECT NON EMPTY NONEMPTYCROSSJOIN([DateOfSale].[Actual].[YearSold].Members,{[Measures].[Units Sold],[Measures].[Amount Sold]}) ON 0,NON EMPTY [Product].%TopMembers ON 1 FROM [HoleFoods]

How can I get an extra column/measure wich shows me [YearSold-1].[Measures].[Amount Sold]-[YearSold-1].[Measures].[Amount Sold] ?

Do I have to do this in the pivot or in the cube and how ?

[Kurt Hofman]

I'm using Caché 2012.1 !

Op vrijdag 11 mei 2012 19:42:38 UTC+2 schreef Kurt Hofman het volgende:

[Lexi Hayden]

You can define a calculated member (either here or in the cube) that
gets the value you want as an MDX expression. Try this one (not sure
if this is most elegant solution):

Save your pivot table and then click the calculator icon. For type,
click measure. Then use this for the expression:
([Measures].[Amount Sold],[dateofsale].[actual].
[yearsold].currentmember.lag(1))-([Measures].[Amount Sold],
[dateofsale].[actual].[yearsold].currentmember)

I used this in a query of a slightly different form than you asked.
Try this:

WITH MEMBER [MEASURES].[SalesDelta] AS '([Measures].[Amount Sold],
[dateofsale].[actual].[yearsold].currentmember.lag(1))-([Measures].
[Amount Sold],[dateofsale].[actual].[yearsold].currentmember)' SELECT
{[Measures].[Amount Sold],[MEASURES].[SALESDELTA]} ON 0,NON EMPTY
NONEMPTYCROSSJOIN([Product].%TopMembers,[DateOfSale].[Actual].
[YearSold].Members) ON 1 FROM [HoleFoods]

The interesting pieces here are the currentmember function and the lag
function.

With this formulation, I couldn't create exactly the query you had
because it seems that we don't support CROSSJOIN with a "calculated
measure." -- Or that's what it looked like to me. Don't take this
diagnosis as a final answer on that!

[Kurt Hofman]

WITH  MEMBER [MEASURES].[SalesDelta] AS '([Measures].[Amount Sold],[dateofsale].[actual].[yearsold].currentmember.lag(1))-([Measures].[Amount Sold],[dateofsale].[actual].[yearsold].currentmember)' SELECT {[Measures].[Amount Sold],[MEASURES].[SALESDELTA]} ON 0,NON EMPTY NONEMPTYCROSSJOIN([Product].%TopMembers,[DateOfSale].[Actual].[YearSold].Members) ON 1 FROM [HoleFoods] 

gives me the result I want but in row-mode, we want them in column mode

If I add ([Measures].[Amount Sold],[dateofsale].[actual]. 

[yearsold].currentmember.lag(1))-([Measures].[Amount Sold], 

[dateofsale].[actual].[yearsold].currentmember) as an measure our column nothing shows in the pivot 

Thanks for your help with this one, it's already one step closer to the solution I think

Op vrijdag 11 mei 2012 20:26:07 UTC+2 schreef Lexi Hayden het volgende:

Op vrijdag 11 mei 2012 20:26:07 UTC+2 schreef Lexi Hayden het volgende:

Op vrijdag 11 mei 2012 20:26:07 UTC+2 schreef Lexi Hayden het volgende:

Op vrijdag 11 mei 2012 20:26:07 UTC+2 schreef Lexi Hayden het volgende:

Op vrijdag 11 mei 2012 20:26:07 UTC+2 schreef Lexi Hayden het volgende:

[Josef Nemecek]

Just in case nobody replied yet: Alter "ON 0" to "ON 1" and "ON 1" to "ON 0". The "ON" says where to put the dimensions levels to, on row headers or column headers. Instead of "ON 0" and "ON 1" you can use "ON ROWS" and "ON COLUMNS", being much more explicit than 0 and 1.

Regards, Josef.

[Kurt Hofman]

Thans very much, that's it !

Op maandag 14 mei 2012 00:31:56 UTC+2 schreef Josef Nemecek het volgende:

[Kurt Hofman]

I've solved it with the MDX-function %CELL in analyzer, it's not perfect but it works just fine

Op vrijdag 11 mei 2012 19:42:38 UTC+2 schreef Kurt Hofman het volgende:

I'm trying to include a year to year-1 comparison (growth/shrink) in a pivot and I'm getting stuck here.