February 3, 2010 at 11:11 am
Simple question: why doesn't return my PriorCashIn?
WITH
SET [PriorPeriod] AS {[Date Dimension].[Fiscal Calendar].[Fiscal Week].&[2009]&[12]&[52].Lag(57) : [Date Dimension].[Fiscal Calendar].[Fiscal Week].&[2009]&[12]&[52].Lag(53)}
MEMBER [Measures].[PriorCashIn] AS
SUM( {[PriorPeriod]}, [Measures].[Cash In])
//SUM( {[Date Dimension].[Fiscal Calendar].[Fiscal Week].&[2009]&[12]&[52].Lag(57) : [Date Dimension].[Fiscal Calendar].[Fiscal Week].&[2009]&[12]&[52].Lag(53)} , [Measures].[Cash In])
SELECT NON EMPTY
{
[Measures].[Cash In]
, [Measures].[PriorCashIn]
} ON COLUMNS,
NON EMPTY
{
[Location].[Store].[Machine]
} ON ROWS
FROM (SELECT [Date Dimension].[Fiscal Calendar].[Fiscal Week].&[2009]&[12]&[52].Lag(4) : [Date Dimension].[Fiscal Calendar].[Fiscal Week].&[2009]&[12]&[52] ON COLUMNS
FROM [Amusements] )
but this does:
WITH
SET [PriorPeriod] AS {[Date Dimension].[Fiscal Calendar].[Fiscal Week].&[2009]&[12]&[52].Lag(57) : [Date Dimension].[Fiscal Calendar].[Fiscal Week].&[2009]&[12]&[52].Lag(53)}
MEMBER [Measures].[PriorCashIn] AS
//SUM( {[PriorPeriod]}, [Measures].[Cash In])
SUM( {[Date Dimension].[Fiscal Calendar].[Fiscal Week].&[2009]&[12]&[52].Lag(57) : [Date Dimension].[Fiscal Calendar].[Fiscal Week].&[2009]&[12]&[52].Lag(53)} , [Measures].[Cash In])
SELECT NON EMPTY
{
[Measures].[Cash In]
, [Measures].[PriorCashIn]
} ON COLUMNS,
NON EMPTY
{
[Location].[Store].[Machine]
} ON ROWS
FROM (SELECT [Date Dimension].[Fiscal Calendar].[Fiscal Week].&[2009]&[12]&[52].Lag(4) : [Date Dimension].[Fiscal Calendar].[Fiscal Week].&[2009]&[12]&[52] ON COLUMNS
FROM [Amusements] )
Why doesn't the set substitution work in the SUM? Is my syntax wrong?
February 3, 2010 at 11:18 am
Out of interest, the two queries you've provided reference different dimensionality for the rows, which may/may not make a difference (it could if the dimension is not related to the current measure group).
Anyways, have you tried running both approaches with no row specification at all? This would prove out that the method (set plus calc member) actually works. At a birds-eye level, the syntax looks ok for both approaches.
Steve.
February 3, 2010 at 11:47 am
Sorry 'bout that, I didn't complete my edits. They have the same dimensionality. The ONLY difference is whether or not I use the "calculated set" in the SUM, or not.
February 5, 2010 at 6:56 pm
bump
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply