Count Comparison Month over Month - doesn't work when filtered

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Analysis Services » Count Comparison Month over Month - doesn't...

Count Comparison Month over Month - doesn't work when filtered

Rate Topic

Display Mode

Topic Options

Author

Message

Chris Becker

Posted Wednesday, October 10, 2012 5:01 PM

SSC Veteran

Group: General Forum Members
Last Login: Friday, February 22, 2013 11:49 AM
Points: 220, Visits: 216

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

Post #1371161

Chris Becker

Posted Thursday, October 11, 2012 1:01 PM

SSC Veteran

Group: General Forum Members
Last Login: Friday, February 22, 2013 11:49 AM
Points: 220, Visits: 216

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

Post #1371741

Mackers

Posted Friday, October 12, 2012 8:43 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 4:19 AM
Points: 117, Visits: 460

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

Post #1372189

« Prev Topic | Next Topic »

Permissions