June 15, 2017 at 3:26 pm
I have a cube that has sales fact, store dimension and date dimension. I need to create a calculated dimension member for an attribute "NewStore" in store dimension. The new member should be used to slice the cube to show new stores based on any date. A new store means there is no sales on the date of previous year. For example, on 2016-06-01, if there is no sales on 2015-06-01 then this is a new store on 2016-06-01, on 2017-02-01, if there is no sales on 2016-02-01 then this is a new store, and so on....
Here is my MDX which returns the right results (giving me the new stores based on [Date].[Year - Qtr - Month - Date].[Year].[2016])
with
Member [Measures].[PY]
as
(
[Measures].[Sales Amt],
PARALLELPERIOD([Date].[Year - Qtr - Month - Date].[Year],
1,
[Date].[Year - Qtr - Month - Date].CurrentMember
)
)
SELECT [Measures].[Sales Amt] on columns,
{ Filter (
[Store].[Store Name].[Store Name],
ISEMPTY(([Date].[Year - Qtr - Month - Date].CurrentMember, [Measures].[PY])) = False
)
} on rows
FROM [MyCube]
where [Date].[Year - Qtr - Month - Date].[Year].[2016];
But when I try to execute following query it doesn't return the new stores but all stores and leave the Sales Amt as (null). I need help to make this query work so I can apply it in calculation in SSAS.
with
Member [Measures].[PY]
as
(
[Measures].[Sales Amt],
PARALLELPERIOD([Date].[Year - Qtr - Month - Date].[Year],
1,
[Date].[Year - Qtr - Month - Date].CurrentMember
)
), format_string = "Currency"
Member [Store].[NewStore].[All].[3] as
Aggregate
(
Filter (
([Store].[Store Name].[Store Name]),
(ISEMPTY(([Date].[Year - Qtr - Month - Date].CurrentMember, [Measures].[PY]))) = True
)
, [Measures].CurrentMember
)
SELECT [Measures].[Sales Amt] on 0,
([Store].[NewStore].[All].[3]) on 1
FROM [MyCube]
where ([Date].[Year - Qtr - Month - Date].[Year].[2016]
,[Store].[Is Same Store].[All].[3]
)
Any helps will be appreciated.
Thanks
Rocky1
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply