CODEWITHSUNDEEP
ssasmdxmeasure
Nisa
Nisa

Reputation: 11

MDX -No Sales over 30 days

I'd like to get the product with zero sales over 30 days. E.g. Below is my expected result: Store,Product,Days Store1, product1, 33 Store1, product2, 100 Store2, product5, 96 Store34, product14, 78 Store100, product9, 47

So I wrote below query：

WITH 
MEMBER [Measures].[Zero Sales Days] 
AS 
COUNT(
     FILTER(
            NONEMPTY( [Calendar].[Date].[Day],[Measures].[POS Qty])                   
             , ( [Measures].[POS Qty]=0)
            ) 
 )

SELECT 
([Store].[Store].[Store],[product].[product].[product]) on 1,
([MEASURES].[Zero Sales Days]) ON 0
FROM [testcube]

The problem is: How to filter the case: days of zero sales<30

Thanks, Nia

I did some change and then ran against my DB. I got nothing if I added the where cause. If not, the result is '#Error'.

I need not select any time related dimension. What I want to do for the report is: select store and product dimension, and define a calculated measure to get the count. Boyan, I will be really appreciated it if you can need the detailed the query for it.

Upvotes: 1

Views: 1492

Answers (2)

Boyan Penev
Boyan Penev

Reputation: 854

The following query works against Adventure Works and shows you the products with no sales for over 30 days from the date in the WHERE clause back:

WITH
MEMBER [Measures].[Number of Periods With No Sales] AS
    Iif(([Date].[Date].CurrentMember, [Measures].[Internet Sales Amount])=0,
        ([Date].[Date].PrevMember, [Measures].[Number of Periods With No Sales])+1,
        NULL
    )
MEMBER [Measures].[Number of > 30 Periods With No Sales] AS
    Sum(
        Iif([Measures].[Number of Periods With No Sales] > 30,
            [Measures].[Number of Periods With No Sales],
            NULL
        )
    )
SELECT
{
    [Measures].[Number of > 30 Periods With No Sales]
} ON 0,
NON EMPTY {
    [Product].[Product Categories].[Product]
} ON 1
FROM [Adventure Works]
WHERE [Date].[Calendar].[Date].&[860]

You will need to re-work it (change the dimension/measure names) to get it to work against your db. Please let me know if you need a query which can give you all products regardless of the date, which have at least one period with more than 30 days with no sales (e.g. max period with no sales, or an arbitrary such period). This will require a few changes. Also, since the query is using recursion it may be slow - if it is too slow we can see how to improve its performance - something which may require changes to your data model to support this bit of analytics.

Upvotes: 1

ic3
ic3

Reputation: 7680

The function LastPeriods is what you're looking for:

 WITH 
  MEMBER [Measures].[Zero Sales Days] 
  AS COUNT(
        FILTER([Calendar].[Date].[Day], 
          SUM( LastPeriods(30, [Calendar].[Date].currentmember),[Measures].[POS Qty]) 
          = 0 )
        ) 
 SELECT 
  ([Store].[Store].[Store],[product].[product].[product]) on 1,
  ([MEASURES].[Zero Sales Days]) ON 0
 FROM [testcube]

Upvotes: 2

Related Questions