How to get number of the same members in mdx?

1
votes

I would like to determine the number of particular weekdays (e.g. Mondays, Tuesdays, and so on....) between two dates. I thought something like the following should work, but member returns 1.

What have i done wrong? 

WITH 
  MEMBER measures.NumberOfSameWeekDays AS 
    Count([Dim Date].[Day Of Week].CurrentMember) 
SELECT 
  measures.NumberOfSameWeekDays ON COLUMNS
 ,[Dim Date].[Day Of Week].[Day Of Week] ON ROWS
FROM [test]
WHERE 
  (
    [Dim Client].[Common Client UID].&[{ED8822E7-2873-4388-BC3A-CC553D939FC4}]
   ,
    [Dim Date].[Date Int].&[20150701] : [Dim Date].[Date Int].&[20150731]
  );
2
sqlmdxcubeolap-cube
this will always return 1: Count( <some hierarchy used on rows>.currentmember) ...because 1 member is currently in the context of the resultset. – whytheq
can you give a code example? – Timsen
.CurrentMember is a function that returns the member on the row - if a row has one member on it, then it returns 1. No example required. In your script for the row that says Tuesday the .Currentmember function is counting how many times it has Tuesday on the Tuesday row .... 1. – whytheq

2 Answers

1
votes

Adventure Works version:

WITH MEMBER Measures.CountOfDays  AS
GENERATE
    (
     EXISTING [Date].[Date].[Date].MEMBERS,
     EXISTING [Date].[Day of Week].[Day of Week].MEMBERS
     ,ALL
    ).COUNT


SELECT Measures.CountOfDays ON 0
,[Date].[Day of Week].[Day of Week].MEMBERS ON 1
FROM [Adventure Works]  
WHERE [Date].[Calendar].&[2005]: [Date].[Calendar].&[2006]

The GENERATE part gets all the days of weeks in current context and based on whatever filter you might have.

1
votes

This is a proof of what is happening:

WITH 
  MEMBER measures.NumberOfSameWeekDays AS 
    Count([Date].[Day of Week].CurrentMember) 
  MEMBER measures.WeekDayCurrentMem AS 
    [Date].[Day of Week].CurrentMember.Member_Caption 
SELECT 
  {
    measures.NumberOfSameWeekDays
   ,measures.WeekDayCurrentMem
  } ON COLUMNS
 ,[Date].[Day of Week].[Day of Week] ON ROWS
FROM [Adventure Works]
WHERE 
    [Date].[Calendar].[Date].&[20050101]
  : 
    [Date].[Calendar].[Date].&[20050116];

Here is the result of the above:

enter image description here

Here is a solution to the above behaviour:

WITH 
  MEMBER measures.NumberOfSameWeekDays AS 
    Count
    (
      (EXISTING 
        [Date].[Day of Week].CurrentMember * [Date].[Calendar].[Date])
    ) 
SELECT 
  {
    measures.NumberOfSameWeekDays
  } ON COLUMNS
 ,[Date].[Day of Week].[Day of Week] ON ROWS
FROM [Adventure Works]
WHERE 
    [Date].[Calendar].[Date].&[20050101]
  : 
    [Date].[Calendar].[Date].&[20050131];

This returns the following:

enter image description here

A simplified version of Sourav's answer - although still rather complex - and potentially slow as it uses Generate which is iterative:

WITH 
  MEMBER Measures.CountOfDays AS 
    Generate
    (
      (EXISTING 
        [Date].[Date].[Date].MEMBERS)
     ,[Date].[Day of Week]
     ,ALL
    ).Count 
SELECT 
  Measures.CountOfDays ON 0
 ,[Date].[Day of Week].[Day of Week].MEMBERS ON 1
FROM [Adventure Works]
WHERE 
  [Date].[Calendar].&[2005] : [Date].[Calendar].&[2006];