MDX for Zero months in last 12 = Number of zero transacting months in the last 12 months rolling window

Question

I am working on MDx query to count the number ZERO txn for each product for last 12 months from today's date. Prepared MDX query for Adventure works fine but my 2nd query running on my Acutal cube working correctly.

Can you please help me out to rectify the query or any filter needed to get correct results like 1st sample query?

1st Sample Query on Adventure Works: (working correctly)

WITH Member [Measures].[Months With Zero Sales] AS 
COUNT(
    FILTER(
        DESCENDANTS(
        {[Date].[Calendar].[Month].&[2008]&[1].LAG(12):[Date].[Calendar].[Month].&[2008]&[1]}, [Date].[Calendar].[Month]) , 
[Measures].[Sales Amount] = 0 ) )
SELECT {[Measures].[Months With Zero Sales]} ON 0, 
[Product].[Product Model Lines].[Product Line].Members on 1
FROM [Adventure Works];

Product Lines  Months With Zero Sales]
-----------    -----------------
Accessory      0
Componenets    0
Mountain       0
Road           0
Touring        6

2nd Query on my Cube:

WITH Member [Measures].[Zero Months in last 12] AS 
COUNT(
      FILTER(
            DESCENDANTS(
            {[Settlement Date].[Calendar].[Settlement Calendar Month].&[2014]&[1].LAG(12):
            [Settlement Date].[Calendar].[Settlement Calendar Month].&[2014]&[1]}, 
            [Settlement Date].[Calendar].[Settlement Calendar Month]),
            [Measures].[Transaction Count] = 0 ) )
SELECT {[Measures].[Transaction Count] ,[Measures].[Zero Months in last 12]} ON 0, 
[Terminal].[terminal ID].members on 1
FROM [cubetxn]

I'm expecting if non zero where "transaction count" is (null), even if there is transaction count not null, there could be chances to have one month with empty tuples.

Can you please correct my query or someone can give me sample query which should able to give me total zero month txn count for last 12 month of each product.

Terminal ID    Transaction Count      Zero Months in last 12
----------     -----------------      -------------------------
All Terminals   504,112,053            0
Q1001             138,832              0
Q1002             (null)               0
Q1003              88,800              0
Q1004             (null)               0

Since we can't execute your query, perhaps you can tell us what it is returning and what you were expecting it to return. There probably isn't enough information in your question as it is, unless someone just wants to take some guesses as to the problem. More information about your model (the measures and dimension attributes being used in the query would also be helpful. — mmarie
Query results sample is given in question, I'm expecting if Transaction count is (null) then "Zero Months in last 12" should display 1. However, Transaction count is sum of txn for last 12 months, so there are chances empty txn months and i want to get those months count where sum of txn was empty — Niel
@Niel good question. Unsure why someone has voted to close. Thanks for using AdvWrks — whytheq

whytheq whytheq · Accepted Answer · 2015-03-19T12:47:08

First comment is that this is a quicker pattern to use when doing a count/filter:

WITH 
  MEMBER [Measures].[Months With Zero Sales] AS 
    Sum
    (
      Descendants
      (
        {
            [Date].[Calendar].[Month].&[2008]&[1].Lag(12)
          : 
            [Date].[Calendar].[Month].&[2008]&[1]
        }
       ,[Date].[Calendar].[Month]
      )
     ,IIF
      (
        [Measures].[Sales Amount] = 0
       ,1
       ,null
      )
    ) 
SELECT 
  {[Measures].[Months With Zero Sales]} ON 0
 ,[Product].[Product Model Lines].[Product Line].MEMBERS ON 1
FROM [Adventure Works];

Based on this more efficient approach does the following work?

WITH 
  MEMBER [Measures].[Zero Months in last 12] AS 
    Sum
    (
      Descendants
      (
        {
            [Settlement Date].[Calendar].[Settlement Calendar Month].&[2014]&[1].Lag(12)
          : 
            [Settlement Date].[Calendar].[Settlement Calendar Month].&[2014]&[1]
        }
       ,[Settlement Date].[Calendar].[Settlement Calendar Month]
      )
     ,IIF
      (
        [Measures].[Transaction Count] = 0
       ,1
       ,0
      )
    ) 
SELECT 
  {
    [Measures].[Transaction Count]
   ,[Measures].[Zero Months in last 12]
  } ON 0
 ,[Terminal].[terminal ID].MEMBERS ON 1
FROM [cubetxn];

If there is still a problem then why not reconstruct your script so you can manually check what to expect. So for the AdvWrks script I'd run this so that I could actually confirm that the 6 is correct:

SELECT 
  {[Measures].[Sales Amount]} ON 0
 ,
    Descendants
    (
      {
          [Date].[Calendar].[Month].&[2008]&[1].Lag(12)
        : 
          [Date].[Calendar].[Month].&[2008]&[1]
      }
     ,[Date].[Calendar].[Month]
    )
  * 
    [Product].[Product Model Lines].[Product Line].MEMBERS ON 1
FROM [Adventure Works];

So against your cube what does the following return?

SELECT 
  {[Measures].[Transaction Count]} ON 0
 ,
    Descendants
    (
      {
          [Settlement Date].[Calendar].[Settlement Calendar Month].&[2014]&[1].Lag(12)
        : 
          [Settlement Date].[Calendar].[Settlement Calendar Month].&[2014]&[1]
      }
     ,[Settlement Date].[Calendar].[Settlement Calendar Month]
    )
  * 
    [Terminal].[terminal ID].MEMBERS ON 1
FROM [cubetxn];

MDX for Zero months in last 12 = Number of zero transacting months in the last 12 months rolling window

1st Sample Query on Adventure Works: (working correctly)

2nd Query on my Cube:

2 Answers