locked
How to get Distinct Count of Products across two dimensions RRS feed

  • Question

  • Hi,

    I have two dimensions, Item and Presentations. I need to get distinct count of products for IMD_Id + Merc_Pres_Id. IMD_Id is the lowest member in Item and Merc_Pres_Id is lowest in Presentation. My MDX query is given below but when I apply filters to slice it, it does not work and does not give right count. It always gives the count for all.

    /* Last Year Demand - Demand for 12 months back of selected months */

    With Member [Measures].[LYDemand] as

    Sum(

    Generate(

    EXISTING[All Date].[Fiscal Month Name].[Fiscal Month Name].Members,

    {parallelperiod([All Date].[Fiscal Month Name].[Fiscal Month Name], 12

    ,[All Date].[Fiscal Month Name].CurrentMember)}

    )

    ,[Measures].[Proj Demand]

    )

     

    /* ********************************************************************************************* */

     

    /* Last to last Year Demand - Demand for 24 back of selected months */

    Member [Measures].[LLYDemand] as

    Sum(

    Generate(

    EXISTING[All Date].[Fiscal Month Name].[Fiscal Month Name].Members,

    {parallelperiod([All Date].[Fiscal Month Name].[Fiscal Month Name], 24

    ,[All Date].[Fiscal Month Name].CurrentMember)}

    )

    ,[Measures].[Proj Demand]

    )

     

    /* ********************************************************************************************* */

    /* Current Year Active Products */

    Member [Measures].[CYCount] as

    CASE WHEN

    [Measures].[Proj Demand] > 0

    THEN

    DistinctCount(Existing(([All Items].[IMD Id].[IMD Id],[All Merchandise Presentations].[Merch Pres Key].[Merch Pres Key])))

    ELSE

    NULL

    END

     

    /* Last year Active Products */

    Member [Measures].[LYCount] as

    CASE WHEN

    [Measures].[LYDemand] > 0

    THEN

    DistinctCount(([All Items].[IMD Id].[IMD Id],[All Merchandise Presentations].[Merch Pres Key].[Merch Pres Key], [All Items].[Style Name].CurrentMember, (StrToMember('[All Date].[Fiscal Month Name].&[201401]',CONSTRAINED).Lag(12) : StrToMember('[All Date].[Fiscal Month Name].&[201411]',CONSTRAINED).Lag(12))))

    ELSE

    NULL

    END

     

    /* Last to last Year Active Products */

    Member [Measures].[LLYCount] as

    CASE WHEN

    [Measures].[LLYDemand] > 0

    THEN

    DistinctCount(([All Items].[IMD Id].[IMD Id],[All Merchandise Presentations].[Merch Pres Key].[Merch Pres Key], [All Items].[Style Name].CurrentMember, (StrToMember('[All Date].[Fiscal Month Name].&[201401]',CONSTRAINED).Lag(24) : StrToMember('[All Date].[Fiscal Month Name].&[201411]',CONSTRAINED).Lag(24))))

    ELSE NULL END

     

    SELECT

    {

    [Measures].[CYCount], [Measures].[LYCount], [Measures].[LLYCount],

    [Measures].[Proj Demand],[Measures].[LYDemand],[Measures].[LLYDemand]

    }

    ON COLUMNS,

    Non Empty([All Items].[Demand Center Name].[Demand Center Name], [All Items].[Style Name].[Style Name]) ON ROWS

    FROM

    (SELECT (StrToSet('[All Items].[Style].[ALL]')) ON COLUMNS

    FROM

    (SELECT (StrToSet('[All Items].[Demand Center].[ALL]')) ON COLUMNS

      FROM

    (select (STRTOSET('[All Items].[Merch Group].&[MG-110]')) on Columns

    FROM

    (SELECT (StrToSet('[All Merchandise Presentations].[Merch Pres Chnl Dkey].&[MPC-1]')) ON COLUMNS

       From

    [FMI Forecasting]

    ))))

    WHERE {strToMember('[All Date].[Fiscal Month Name].&[201401]',CONSTRAINED) : StrToMember('[All Date].[Fiscal Month Name].&[201411]',CONSTRAINED)}

    Requirements are as follows:

    1. Distinct Count should not include products where Proj Demand is 0, when I am using Filter function to remove products with 0 demand, query is really slow and execution time goes up from 35- 40 secs to 8-9 Minutes.

    2. When we apply filter (parameters) Distinct Count should be in the context of filters( which are mentioned in the select statement like Style, Demand Center and Merch Group). Currently after applying filters count does not change.

    Thanks for help.

    Wednesday, June 18, 2014 5:28 PM

All replies

  • Hi Skd78,

    Thank you for your question. 

    I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated. 

    Thank you for your understanding and support.

    Regards,


    Charlie Liao
    TechNet Community Support

    Thursday, June 19, 2014 7:38 AM
  • Hi Skd78,

    From a support perspective this is really beyond what we can do here in the forums. If you cannot determine your answer here or on your own, consider opening a support case with us. Visit this link to see the various support options that are available to better meet your needs:

    http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone

    Regards


    Tuesday, June 24, 2014 2:06 AM