October 10, 2012 at 5:01 pm
My first dive into MDX. I have a calculation which works when 2 entire months are compared. When I filter the Transaction Date to compare e.g. September Days 1 to 10 to October Days 1 to 10, this calc does not work. It still shows a difference in count of 30 days in September (entire month) vs October, so counts are ALL negative. How do I resolve? Tia!!
Case
// Test for current coordinate being on (All) member.
When [Transaction Date].[Date Hierarchy].CurrentMember.Level Is
[Transaction Date].[Date Hierarchy].[(All)]
Then "NA"
// Test to avoid division by zero.
When IsEmpty
(
(
ParallelPeriod
(
[Transaction Date].[Date Hierarchy].[Month],
1,
[Transaction Date].[Date Hierarchy].CurrentMember
),
[Measures].[Checks Count]
)
)
Then Null
Else (
( [Transaction Date].[Date Hierarchy].CurrentMember,
[Measures].[Checks Count])
-
(
ParallelPeriod
(
[Transaction Date].[Date Hierarchy].[Month],
1,
[Transaction Date].[Date Hierarchy].CurrentMember
),
[Measures].[Checks Count]
)
)
End
Chris Becker bcsdata.net
October 11, 2012 at 1:01 pm
Would be nice to have something more re-usable. I found this code with a google search, however is limited when drilling through Transaction Date dim, will not work when Year or Quarter selected.
Do I need to create another calculated measure for these?? Seems to clutter up measures group fast and is not flexible
Thanks in advance for any help,
Chris
Chris Becker bcsdata.net
October 12, 2012 at 8:43 am
Is this not working for September Days 1 to 10 or are you looking for an aggregated view of September Days 1 to 10?
The query looks like it should work fine for each individual days...
Mack
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply