Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Count Comparison Month over Month - doesn't work when filtered Expand / Collapse
Author
Message
Posted Wednesday, October 10, 2012 5:01 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, July 7, 2014 12:10 PM
Points: 222, Visits: 241

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
Posted Thursday, October 11, 2012 1:01 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, July 7, 2014 12:10 PM
Points: 222, Visits: 241

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
Posted Friday, October 12, 2012 8:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 13, 2014 7:34 AM
Points: 117, Visits: 499
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 »

Add to briefcase

Permissions Expand / Collapse