December 29, 2008 at 11:33 am
I am new to MDX, and can't for the life of me figure out how to get the SAME parallel period as my filter. My goal is to use this MDX query for a SSRS report. I can specify the parameters as needed, but first would like to get the MDX query working properly. I have been stuck on this for a few days.
The Parallel Period gets me the entire previous Year, but I want it to match my WHERE Range. Any Ideas?
Thanks
Greg
Query Below:
WITH MEMBER [Measures].[Margin] AS ([Measures].[Sale Amt - Shipped Fact]-[Measures].[Cost Amt - Shipped Fact]), FORMAT_STRING = "Currency"
MEMBER [Measures].[LYMargin] As
(
PARALLELPERIOD(
[Shipped Time].[Year - Quarter - Month - Date - Date Time - Is Holiday - Is Weekday].[Year]
, 1
, [Shipped Time].[Year - Quarter - Month - Date - Date Time - Is Holiday - Is Weekday].CurrentMember)
,[Measures].[Margin]), FORMAT_STRING = "Currency"
MEMBER [Measures].[GM] AS (IIF([Measures].[Sale Amt - Shipped Fact] IS NULL OR IsEmpty([Measures].[Sale Amt - Shipped Fact]) OR [Measures].[Sale Amt - Shipped Fact] = 0, NULL, [Measures].[Sale Amt - Shipped Fact]-[Measures].[Cost Amt - Shipped Fact])/[Measures].[Sale Amt - Shipped Fact]), FORMAT_STRING = "Percent"
MEMBER [Measures].[LYGM] As
(
PARALLELPERIOD(
[Shipped Time].[Year - Quarter - Month - Date - Date Time - Is Holiday - Is Weekday].[Year]
, 1
, [Shipped Time].[Year - Quarter - Month - Date - Date Time - Is Holiday - Is Weekday].CurrentMember)
,[Measures].[GM]), FORMAT_STRING = "Percent"
MEMBER [Measures].[Cost] AS ([Measures].[Cost Amt - Shipped Fact]) , FORMAT_STRING = "Currency"
MEMBER [Measures].[LYCost] AS
(
PARALLELPERIOD(
[Shipped Time].[Year - Quarter - Month - Date - Date Time - Is Holiday - Is Weekday].[Year]
, 1
, [Shipped Time].[Year - Quarter - Month - Date - Date Time - Is Holiday - Is Weekday].CurrentMember)
,[Measures].[Cost]), FORMAT_STRING = "Currency"
MEMBER [Measures].[CostPct] AS(IIF([Measures].[LYCost] IS NULL OR IsEmpty([Measures].[LYCost]) OR [Measures].[LYCost] = 0, NULL, ([Measures].[Cost]-[Measures].[LYCost])/[Measures].[LYCost])), FORMAT_STRING = "Percent"
MEMBER [Measures].[CostPctTtl] AS
( [Measures].[Cost]/( [Measures].[Cost], Axis(1)(0)(Axis(1)(0)).Dimension.CurrentMember.Parent) ), FORMAT_STRING = "Percent"
MEMBER [Measures].[Sales] AS ([Measures].[Sale Amt - Shipped Fact]) , FORMAT_STRING = "Currency"
MEMBER [Measures].[LYSales] As
(
PARALLELPERIOD(
[Shipped Time].[Year - Quarter - Month - Date - Date Time - Is Holiday - Is Weekday].[Year]
, 1
, [Shipped Time].[Year - Quarter - Month - Date - Date Time - Is Holiday - Is Weekday].CurrentMember)
,[Measures].[Sales]), FORMAT_STRING = "Currency"
MEMBER [Measures].[SalesPct] AS (IIF([Measures].[LYSales] IS NULL OR IsEmpty([Measures].[LYSales]) OR [Measures].[LYSales] = 0, NULL, ([Measures].[Sales]-[Measures].[LYSales])/[Measures].[LYSales])), FORMAT_STRING = "Percent"
MEMBER [Measures].[SalesPctTtl] AS ([Measures].[Sales]/([Measures].[Sales], Axis(1)(0) (Axis(1)(0)).Dimension.CurrentMember.Parent) ), FORMAT_STRING = "Percent"
MEMBER [Measures].[Units] AS ([Measures].[Sale Qty - Shipped Fact]), FORMAT_STRING="###,###,###,###"
MEMBER [Measures].[LYUnits] As
(
PARALLELPERIOD(
[Shipped Time].[Year - Quarter - Month - Date - Date Time - Is Holiday - Is Weekday].[Year]
, 1
, [Shipped Time].[Year - Quarter - Month - Date - Date Time - Is Holiday - Is Weekday].CurrentMember)
,[Measures].[Units]), FORMAT_STRING="###,###,###,###"
MEMBER [Measures].[UnitsPct] AS (IIF([Measures].[LYUnits] IS NULL OR IsEmpty([Measures].[LYUnits]) OR [Measures].[LYUnits] = 0, NULL, ([Measures].[Units]-[Measures].[LYUnits])/[Measures].[LYUnits])), FORMAT_STRING = "Percent"
MEMBER [Measures].[UnitsPctTtl] AS ([Measures].[Units]/([Measures].[Units], Axis(1)(0) (Axis(1)(0)).Dimension.CurrentMember.Parent) ), FORMAT_STRING = "Percent"
SELECT
NON EMPTY {
[Measures].[GM],
[Measures].[LYGM],
[Measures].[Cost],
[Measures].[LYCost],
[Measures].[CostPct],
[Measures].[CostPctTtl],
[Measures].[Margin],
[Measures].[LYMargin],
[Measures].[Sales],
[Measures].[LYSales] ,
[Measures].[SalesPct],
[Measures].[SalesPctTtl],
[Measures].[Units],
[Measures].[LYUnits],
[Measures].[UnitsPct],
[Measures].[UnitsPctTtl]} ON COLUMNS,
NON EMPTY {
([Product Dimension].[Department - Class - Sub Class - Category].[Department])
*
[Shipped Time].[Year].[Year].[Calendar 2008] } ON ROWS
FROM [JBI Warehouse MF]
WHERE ([Shipped Time].[Month].[Month].[January 2008]:
[Shipped Time].[Month].[Month].[November 2008])
November 4, 2010 at 8:46 am
Hi, Greg,
I solved a very similar problem using "Lag":
WITH
MEMBER [Measures].[Hist Sales] AS
(
[Date].[Planning Hierarchy].CurrentMember.Lag(364)
, [Measures].[Item Sales]
)
, FORMAT = "Currency"
"Lag" moves you laterally along the current level of a hierarchy. I'm at the "Date" level, so I'm moving back exactly 52 weeks.
If you're at weeks and you want the value for one year ago, you would lag 52, months, lag 12, etc.
Hope that helps.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply