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

Distinct Count Measure Group not distinct in MDX script Expand / Collapse
Author
Message
Posted Saturday, February 02, 2013 5:17 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 30, 2013 1:16 PM
Points: 2, Visits: 29
I am attempting run the following MDX query to determine the distinct amount of transaction numbers for a set including 2 dimension attributes. (POS and Concierge) When I run the query, I seem to be getting the distinct amounts for each added to together. If the same transaction number exists in both, they are both counted. However if I attempt to get the same result using an Excel Pivot table, I get the desired transaction number count. Could anyone help me with getting this result in the MDX query?

WITH

SET [Week] AS StrToMember("[Date].[Hierarchy - Fiscal].[Fiscal Week].&[2012]&[48]")

SET [Store] AS {[Financial Transaction Channel].[Transaction Channel Desc].&[POS],
[Financial Transaction Channel].[Transaction Channel Desc].&[CONCIERGE]}



MEMBER [Store Transactions With Returns] AS SUM([Week] * [Store], [Measures].[Operational Transactions Count])


SELECT
{
[Store Transactions With Returns]
} ON COLUMNS,
{
[Week]
} ON ROWS
FROM EDW

Results: Store Transactions With Returns
Wk. 48 - 2012 78,636

The same attempt using an Excel Pivot table yields this:

Row Labels	Operational Transactions Count	
Year 2012 78,368
Half 2 - 2012 78,368
Qtr. 4 - 2012 78,368
December - 2012 78,368
Wk. 48 - 2012 78,368 Result of addition: 78,636
1 US STORES 78,368
CONCIERGE 3,668
POS 74,968
Grand Total 78,368

If you add the POS and Concierge amounts, you get 78,636. Which is the same result I am getting from my MDX query, but after I went through the transactions line by line, I was able to determine that 78,368 is the correct amount of distinct transaction numbers.


Any help on this would be greatly appreciated.
Post #1414973
Posted Friday, February 08, 2013 5:38 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
Try this and let me know if it works

SELECT
{ [Measures].[Operational Transactions Count]} ON COLUMNS,
{ [Date].[Hierarchy - Fiscal].[Fiscal Week].&[2012]&[48]} ON ROWS
FROM
(SELECT {[Financial Transaction Channel].[Transaction Channel Desc].&[POS],
[Financial Transaction Channel].[Transaction Channel Desc].&[CONCIERGE]} ON COLUMNS
FROM EDW)

Mack
Post #1417631
Posted Friday, February 08, 2013 7:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 30, 2013 1:16 PM
Points: 2, Visits: 29
I ended up creating a new dataset and basically using what you describe above. When the dimension attributes are placed in the WHERE clause, it works.

Thanks for your feedback.
Post #1417697
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse