April 21, 2014 at 3:52 pm
Hi All:
I've looked on the web and found many examples of rolling time periods that suggest doing one of the two commands below. However, the measure always comes back as #error. I'm sure I'm just not seeing it... can anyone tell my why these two statements don't work? It's an attempt to sum the last 30 days from Feb 24th. Using a range (first command) and also using the LastPeriods functions both return #error.
\\ Command with date range
WITH
MEMBER [Measures].[Gallons in Last 30 Days] AS
Sum
(
{
[Start Date].[Calendar Hierarchy].[Date].Lag(29)
:
[Start Date].[Calendar Hierarchy].[Date].CurrentMember
}
,[Measures].[Quantity In Gallons]
)
SELECT
{
[Measures].[Quantity In Gallons]
,[Measures].[Gallons in Last 30 Days]
} ON COLUMNS
FROM [cube]
WHERE
[Start Date].[Calendar Hierarchy].[Date].&[20140224];
\\Command with LastPeriods function
WITH
MEMBER [Measures].[Gallons in Last 30 Days] AS
sum(Lastperiods(29,[CIP Start Date].[Calendar Hierarchy].[Date].currentmember),[Measure].[Quantity In Gallons])
SELECT
{
[Measures].[Quantity In Gallons]
,[Measures].[Gallons in Last 30 Days]
} ON COLUMNS
FROM [vEMI_CIP]
WHERE
[CIP Start Date].[Calendar Hierarchy].[Date].&[20140224];
Cheers and thanks,
Simon
April 21, 2014 at 4:52 pm
Nevermind... figured it out about 10 minutes after posting.;-) I just learned the difference between a member statement and a hierarchy statement.
Cheers,
Simon
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply