SSAS MDX Query for date comparision data

  • Hi friends,

    Can anyone help me out for writing MDX for finding products which are present for current date but not in previous date. Below is SQL query for the same.

    select count(distinct Product) from table_A

    where Date='2012-12-12'

    and Product not in (select distinct Product from table_A

    where Date='2012-12-01')

    thanks in advance...

    ----Swapnil

  • Try something along the lines of

    with

    set [Product List] As

    NONEMPTY(

    {[Product].[Product Level 4].[All].Children}

    , ([Measures].[Revenue],[Date].[Day].&[20121212])

    )

    -

    NONEMPTY(

    {[Product].[Product Level 4].[All].Children}

    , ([Measures].[Revenue],[Date].[Day].&[20121201])

    )

    select [Product List] on rows,

    {[Date].[Day].&[20121201],[Date].[Day].&[20121212]} on columns

    from [Your Cube]

    where ([Measures].[Revenue])

    Mack

  • Thank you for your reply...

    Here "Date" is dimention and count(distinct product) i calculated as measure.

    ---Swapnil

  • Hi Mack,

    thank you for your help. Working fine now...

    ....Swapnil

  • Hi All,

    Can anyone help me out in this.

    I have below result,

    C1C1 C1

    26-Nov-12 3-Dec-12 10-Dec-12

    Count Of Product 196 162 210

    % L Count 45.18%40.49% 49.49%

    % C Count 54.82%59.51% 79.51%

    % New Items 0.00%0.00% 0.00%

    Here in % New items i want to write MDX to show the product present for 10-Dec-12 but not in 3-Dec-12,03-Dec-12 but not in 26-Nov-12 according to this product present for 26-Nov-12 are 100% bcoz no previous date is selected.

    thanks

    Swapp

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply