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, Mar 19 '15 at 01:44
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, Mar 19 '15 at 02:37
@Niel good question. Unsure why someone has voted to close. Thanks for using AdvWrks — whytheq, Mar 19 '15 at 12:38

whytheq · Answer 1 · 2015-03-19T12:53:37.007

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];

This query return nothing, just blank column name "Transaction Count" 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]; — Niel, Mar 20 '15 at 08:33
Your provided first query giving results with 2 columns [Transaction Count] & [Zero Months in last 12] but again even trnsaction count sum is (null), [Zero Months in last 12] shows (null) where I'm expecting 1 or more count Transaction Count Zero Months in last 12 All Terminals 504,112,053 (null) A10001 138,832 (null) A10002 (null) (null) A10003 85,762 (null) A10004 (null) (null) A10005 (null) (null) A10006 23,270 (null) — Niel, Mar 20 '15 at 08:36
@Niel please can you not add scripts/results in the _comments_ of posts?! Much easier for us if additional Scripts/results are added as well FORMATTED edits to your original post. — whytheq, Mar 20 '15 at 08:46
I'm sorry but I was unable to find the way to add those responses in my original questions. Will try now — Niel, Mar 21 '15 at 21:55
@Niel maybe with your reputation points you're not able to edit: I'm not sure. There should be a button "Edit" underneath your original question. Anyhow - what happens if you run the last script in my post? — whytheq, Mar 22 '15 at 09:39

score 0 · Answer 2 · answered Mar 22 '15 at 12:40

//Total Transactions count Weekend Sunday (i’ve date dimesion which holds day_in_week=’Sun’ for respective dates)

WITH SET AllTranSundays as
EXISTS(DATE.DATE.DATE.MEMBERS, DATE.DATE.day_in_week.&[Sun], "Sales")
//Count of all sundays which had transactions. 
MEMBER Measures.CntAllTranSundays AS
COUNT(AllTranSundays)

//YTD Days = Count of days within calendar year to date

MEMBER Measures.CntDaysYTD as 
COUNT(DESCENDANTS(ANCESTOR([DATE].[date].CURRENTMEMBER, 
3), 3).item(0).item(0) 
: [DATE].[date].CURRENTMEMBER)

//YTD Transactions = Transaction count year to date

MEMBER Measures.CntTranYTD as 
COUNT(
EXISTS(
{DESCENDANTS(ANCESTOR([DATE].[date].CURRENTMEMBER, 
3), 3).item(0).item(0) 
: [DATE].[date].CURRENTMEMBER}, , "Sales"
)

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

MEMBER Measures.CntZeroTransactingMonths as
COUNT(
    {ANCESTOR([DATE].[date].CURRENTMEMBER, 1).ITEM(0).LAG(12) : ANCESTOR([DATE].[date].CURRENTMEMBER, 1).ITEM(0)}
    -
    EXISTS({ANCESTOR([DATE].[date].CURRENTMEMBER, 1).ITEM(0).LAG(12) : ANCESTOR([DATE].[date].CURRENTMEMBER, 1).ITEM(0)}
    , , 
    "Sales")
)

//Consecutive zero months = Number of consecutive zero transacting months in last 12 months rolling window

Question Do you want a count of instances when consecutive months had zero transactions. Or do you want a count of number of months which had consecutively no transactions?

What I am trying to say is say Jan, March, June, July, August, October, November had no transactions in the rolling last 12 months As per case 1: the answer should be 2 As per case 2: the answer should be 5.

Will update my answer as per your clarification.

SELECT 
{Measures.CntAllTranSundays, Measures.CntDaysYTD, Measures.CntTranYTD, Measures.CntZeroTransactingMonths} ON 0
FROM [YourCube]
where 
[date].[date].[date].&[02/28/2015]

Thanks for all the responses Sourav. For Zero months, i need count of instances when consecutive months had zero transactions. I would really appreciate, if you can give me all above queries with as MDX. I want fetch those results in T-SQL procedure to build up one complex report. — Niel, Mar 23 '15 at 13:04
one more question if you can help, i want WTD & MTD. if suppose my report is running on 23rd March i should get last one week txn sum of each product. Similarly MTD means 1st of March till 23rd txn count of each product — Niel, Mar 23 '15 at 13:06
@Niel - so, per my example, would the answer be 2 or 5? By the way, are yo getting correct counts till now? That would give me the required confidence to go ahead ;-) — SouravA, Mar 23 '15 at 13:33
sorry Sourav, I'll run these query today and respond with results. As per your example, answer would be 2 — Niel, Mar 24 '15 at 20:47
Hi Sourav, would you please provide me the consecutive months zero count MDx query. I need MDX query. — Niel, Apr 02 '15 at 03:58

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 Answers2