March 13, 2015 at 12:46 pm
A snapshot cube has three dimensions: Status (inactive, active), month, member, the measure is count (count of member).
How do I calculate # of members who were inactive or not existing in the previous month, but active in the current month?
Below is the sample code I used to get the count of active members in one month.
select
FROM [SnapshotCube]
where (
{[Status].[Status].&[Active]},
{[Month].[Year Month Hierarchy].[Calendar Year].&[2015].&[2015Q1].&[201503]}
)
March 17, 2015 at 3:03 pm
How about something like this:
...
where{
{{[Month].[Year Month Hierarchy].[Calendar Year].&[2015].&[2015Q1].&[201503]}
* {[Status].[Status].&[Active]}
}
+
{
{{[Month].[Year Month Hierarchy].[Calendar Year].&[2015].&[2015Q1].&[201502]}
* {[Status].[Status].&[Inactive]}
}
MDX Reference: https://msdn.microsoft.com/en-us/library/ms145493.aspx
March 23, 2015 at 8:36 pm
Martin,
Your filter returns the union of March active members and February Inactive members, not the "intersection".
I use the following code to get the result. I feel it rather complicated but could not find a simpler solution.
With MEMBER MEASURES.TEST AS
COUNT
(
NONEMPTY
(
NONEMPTY
(
[Member].[Member ID].[Member ID].MEMBERS,
(
[Status].[Status].&[Active], [Measures].[Member Count]
)
),
(
[Measures].[Member Count],
[Status].[Status].&[InActive],
[Month].[Year Month Hierarchy].CurrentMember.PrevMember
)
)
)
select
{
[Measures].Test
}
on columns,
{[Month].[Year Month Hierarchy].&[2015].&[2015Q1].&[201503]}
on rows
FROM [SnapshotCube]
March 23, 2015 at 8:38 pm
It is a repost of my code with formatting.
With MEMBER MEASURES.TEST AS
COUNT
(
NONEMPTY
(
NONEMPTY
(
[Member].[Member ID].[Member ID].MEMBERS,
(
[Status].[Status].&[Active], [Measures].[Member Count]
)
),
(
[Measures].[Member Count],
[Status].[Status].&[InActive],
[Month].[Year Month Hierarchy].CurrentMember.PrevMember
)
)
)
select
{
[Measures].Test
}
on columns,
{[Month].[Year Month Hierarchy].&[2015].&[2015Q1].&[201503]}
on rows
FROM [SnapshotCube]
March 24, 2015 at 10:27 am
I also tried creating a calculated member combining a member's status in two continuous months. So far I could not figure it out. This may not be feasible because the combination will basically create a new dimension with different grain other the existing Status dimension (and any other dimension).
March 25, 2015 at 9:23 am
seaport (3/24/2015)
I also tried creating a calculated member combining a member's status in two continuous months. So far I could not figure it out. This may not be feasible because the combination will basically create a new dimension with different grain other the existing Status dimension (and any other dimension).
Ooh...I think that may be possible and an interesting exercise. I'll try it a bit later and post results. Post your attempts too if possible.
March 27, 2015 at 4:06 pm
My first try is to do something like
WITH
MEMBER [Product].[Beer and Wine].[BigSeller] AS
IIf([Product].[Beer and Wine] > 100, "Yes","No")
SELECT
{[Product].[BigSeller]} ON COLUMNS,
Store.[Store Name].Members ON ROWS
FROM Sales
It does not work because I can only do simply one-to-one conversion logics and there is probably no way to reference the status of a previous month.
March 27, 2015 at 4:15 pm
My second try is
[Code]
With MEMBER [Status].[Status].[All].[Inactive To Active] AS
COUNT
(
NONEMPTY
(
NONEMPTY
(
[Member].[Member ID].[Member ID].MEMBERS,
(
[Status].[Status].&[Active], [Measures].[Member Count]
)
),
(
[Measures].[Member Count],
[Status].[Status].&[InActive],
[Month].[Year Month Hierarchy].CurrentMember.PrevMember
)
)
)
select
{
[Status].[Status].[Inactive To Active]
}
on columns,
{[Month].[Year Month Hierarchy].&[2015].&[2015Q1].&[201503]}
on rows
FROM [SnapshotCube]
[/Code]
The code returns (null).
The code is basically the the same as my previous one. The only difference is that,
the previous code adds the calculated member to the Measures dimension,
this one adds the calculated member to the Status dimension Status Attribute.
April 6, 2015 at 9:35 am
Apologies for not responding yet, but I am struggling to find a suitable data set for testing. I'll hopefully get something done this week.
April 10, 2015 at 5:41 pm
Ok...finally have some additional feedback. I tried a few different options, and in the end it wasn't possible to easily create a string with the status of this month and the previous month...for comparison.
Using your original syntax, I did end up with some double counts as it was counting the "All" member in some cases...but that could have been because of the difference between my test cube and yours.
Here's my final syntax (not necessarily less complex, but it did count distinctly and accurately):
member [Measures].[Test]
as
distinctcount
(
exists
(
exists
(
{[Member].[Member Name].[Member Name].members}
,{[Month].[Month Number].CurrentMember}
* {[Status].[Status Name].&[Active]}
,"<measure group>"
)
,exists
(
{[Member].[Member Name].[Member Name].members}
,{[Month].[Month Number].currentmember.prevmember}
* {[Status].[Status Name].&[Inactive]}
,"<measure group>"
)
)
)
For me, the "exists" syntax does read a little easier...but that's more of a preference than anything else.
Hope this helps.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply