MDX filtration by more granular value

Question

I have a MDX query:

    SELECT
    ({[Measures].[Sales Count]}) ON COLUMNS, 
    {Filter([Creation Date].[Day].Members, 
    ([Creation Date].[Day].CurrentMember.Name >= '2016/09/01' AND 
    [Creation Date].[Day].CurrentMember.Name <= '2016/09/05'))} ON ROWS  
           FROM [Date Sales Schema]

It allows me to select sales count for date range. Also I might filtering by minutes by replace [Creation Date].[Day] in query to [Creation Date].[Minute]. BUT if I want filtering by minutes, and get result with sales count by days, I dont know, what query must I write?

For example: Filtering from September 8 - 15:57 until September 10 - 13:35 - the result should be:

the number of sales of the 8th September 15:57 - 23: 59
the number of sales of the 9th September at 24 hours
the number of sales of the 10th September 00:00 - 13:35

Such query is near the decision:

SELECT 
  {[Measures].[Sales Count]} ON COLUMNS
 ,Exists
  (
    [Creation Date].[Day].MEMBERS
   ,Filter
    (
      [Creation Date].[Minute].MEMBERS
     ,
        [Creation Date].[Minute].CurrentMember.Name >= '2016/09/01 15:57'
      AND 
        [Creation Date].[Minute].CurrentMember.Name <= '2016/09/10 15:57'
    )
  ) ON ROWS
FROM [Date Sales Schema];

BUT, for example, if I if a sale was made in 10.02.2016 23:55 , and the filter begins on 10.02.2016 23:54 , the result of a request to show me all the sales for 10.02.

Next query is calculate the sum of all period and print the sum as measure for every day-dimension:

WITH MEMBER [Measures].[Day Period Sales Count] AS Aggregate(Filter
    (
      [Creation Date].[Minute].MEMBERS
     ,
        [Creation Date].[Minute].CurrentMember.Name >= '2016/09/01 07:27'
      AND 
        [Creation Date].[Minute].CurrentMember.Name <= '2016/09/01 15:57'
    )
)
SELECT 
([Measures].[Day Period Sales Count]) ON COLUMNS ,{[Creation Date].[Day].[2016/09/01],[Creation Date].[Day].[2016/09/02]} ON ROWS
FROM [Date Sales Schema]

But it do not divide results by days

whytheq whytheq · Accepted Answer · 2016-09-12T12:06:50

You could put the minute filter in a sub-select:

SELECT 
  {[Measures].[Sales Count]} ON COLUMNS
 ,{
    Filter
    (
      [Creation Date].[Day].MEMBERS
     ,
        [Creation Date].[Day].CurrentMember.Name >= '2016/09/01'
      AND 
        [Creation Date].[Day].CurrentMember.Name <= '2016/09/05'
    )
  } ON ROWS
FROM 
(
  SELECT 
    [Creation Date].[Minute].&[1] : [Creation Date].[Minute].&[10] ON 0
  FROM [Date Sales Schema]
);

Without a subselect:

SELECT 
  {[Measures].[Sales Count]} ON COLUMNS
 ,Exists
  (
    [Creation Date].[Day].MEMBERS
   ,Filter
    (
      [Creation Date].[Minute].MEMBERS
     ,
        [Creation Date].[Minute].CurrentMember.Name >= '2016/09/01 15:57'
      AND 
        [Creation Date].[Minute].CurrentMember.Name <= '2016/09/10 15:57'
    )
  ) ON ROWS
FROM [Date Sales Schema];

Try this:

WITH 
  MEMBER [Measures].[Day Period Sales Count] AS 
    Aggregate
    (
      (EXISTING 
        Filter
        (
          [Creation Date].[Minute].MEMBERS
         ,
            [Creation Date].[Minute].CurrentMember.Name >= '2016/09/01 07:27'
          AND 
            [Creation Date].[Minute].CurrentMember.Name <= '2016/09/01 15:57'
        ))
     ,[Measures].[Sales Count]
    ) 
SELECT 
  [Measures].[Day Period Sales Count] ON COLUMNS
 ,{
    [Creation Date].[Day].[2016/09/01]
   ,[Creation Date].[Day].[2016/09/02]
  } ON ROWS
FROM [Date Sales Schema];

MDX filtration by more granular value

2 Answers