How to mimic SQL subtraction of results from two different queries in mdx

Question

I wanted to do the trend analysis between the dates. For an instance current date- 30 days 30-60 days and so on.Below is the snippet of comparable sql query but same I wanted to do in MDX.

SQL

SELECT
ROUND
(
    (
        (
            (
            SELECT
                SUM(del_pri_impr)
            FROM
                reporting.so_sli_calc_val a,
                reporting.user_group_tenant b,
                reporting.salesorder c
            WHERE
                created_on BETWEEN DATE(now()-30) AND DATE(now())
            )

            -

            (            
            SELECT
                SUM(del_pri_impr)
            FROM
                reporting.so_sli_calc_val a,
                reporting.user_group_tenant b,
                reporting.salesorder c
            WHERE
                created_on BETWEEN DATE(now()-60) AND DATE(now()-30)             
            )
        )

        /

        (
                   SELECT
                       SUM(del_pri_impr)
                   FROM
                       reporting.so_sli_calc_val a,
                       reporting.user_group_tenant b,
                     reporting.salesorder c
                 WHERE
                     created_on BETWEEN DATE(now()-60) AND DATE(now()-30)
        ) *100
    )

,
0
) AS trend

MDX:

WITH
 SET [~FILTER] AS
    {[Created_Date.Created_Hir].[Created_On].[2014-04-01]:[Created_Date.Created_Hir].[Created_On].[2014-04-30]}
  SET [~ROWS] AS
  {[Sales Order Attributes SO.Sales_order].[Sales Order ID].Members}
  SELECT
   NON EMPTY {[Measures].[CONT_AMT_GROSS], [Measures].[CONT_AMT_NET]} ON     COLUMNS,
   NON EMPTY [~ROWS] ON ROWS
   FROM [SALES_ORDER]
  WHERE [~FILTER]

As of now I have hard coded the dates, that will come from parameters. I am facing difficulty in creating the second set and how to do subtraction between two sets in MDX.

Answer 1

Not sure if I totally agree with Sourav's answer as I think some form of aggregation will be needed; creating tuples with sets in them may raise an exception.

Here is a simple model, against AdvWrks, that is tested and will do a subtraction for you:

WITH 
  SET [Set1] AS 
      [Date].[Calendar].[Date].&[20060301]
    : 
      [Date].[Calendar].[Date].&[20070308] 
  SET [Set2] AS 
      [Date].[Calendar].[Date].&[20070308]
    : 
      [Date].[Calendar].[Date].&[20080315]
  MEMBER [Date].[Calendar].[All].[Set1Agg] AS 
     aggregate([Set1])
  MEMBER [Date].[Calendar].[All].[Set2Agg] AS 
     aggregate([Set2])

  MEMBER [Date].[Calendar].[All].[x] AS 
    (
      [Date].[Calendar].[All].[Set1Agg]
     ,[Measures].[Internet Sales Amount]
    ) 
  MEMBER [Date].[Calendar].[All].[y] AS 
    (
      [Date].[Calendar].[All].[Set2Agg]
     ,[Measures].[Internet Sales Amount]
    ) 
  MEMBER [Date].[Calendar].[All].[x-y] AS 
    [Date].[Calendar].[All].[x] - [Date].[Calendar].[All].[y] 
SELECT 
  {
    [Date].[Calendar].[All].[x]
   ,[Date].[Calendar].[All].[y]
   ,[Date].[Calendar].[All].[x-y]
  } ON 0
 ,[Product].[Category].[Category] ON 1
FROM [Adventure Works];

Reflecting against your code maybe something like the following:

WITH 
  SET [Set1] AS 
      [Created_Date.Created_Hir].[Created_On].[2014-04-01]
    : 
      [Created_Date.Created_Hir].[Created_On].[2014-04-30] 
  SET [Set2] AS 
      [Created_Date.Created_Hir].[Created_On].[2014-03-01]
    : 
      [Created_Date.Created_Hir].[Created_On].[2014-03-31] 
  MEMBER [Created_Date.Created_Hir].[All].[Set1Agg] AS 
    Aggregate([Set1]) 
  MEMBER [Created_Date.Created_Hir].[All].[Set2Agg] AS 
    Aggregate([Set2]) 
  MEMBER [Measures].[~Last30Days - Now] AS 
    (
      [Created_Date.Created_Hir].[All].[Set1Agg]
     ,[Measures].[SomeMeasure]
    ) 
  MEMBER [Measures].[~Last60Days - Last30Days] AS 
    (
      [Created_Date.Created_Hir].[All].[Set2Agg]
     ,[Measures].[SomeMeasure]
    ) 
  MEMBER [Measures].[~Measure] AS 

        ([Measures].[~Last30Days - Now] - [Measures].[~Last60Days - Last30Days])
      / 
        [Measures].[~Last60Days - Last30Days]
    * 100 
   ,format_string = '#,##0' 
  SET [~ROWS] AS 
    {
      [Sales Order Attributes SO.Sales_order].[Sales Order ID].MEMBERS
    } 
SELECT 
  NON EMPTY 
    {
      [Measures].[CONT_AMT_GROSS]
     ,[Measures].[CONT_AMT_NET]
     ,[Measures].[~Measure]
    } ON COLUMNS
 ,NON EMPTY 
    [~ROWS] ON ROWS
FROM [SALES_ORDER]
WHERE 
  [~FILTER];