locked
MDX to find sales till selected date RRS feed

  • Question

  • Hi,

    I have sales from 2010 to today in my cube. my question is i want to see sales amount from 2010 to 2014 if i select 2014. or if I select 201405 ( may 2014), i want to see sales from 20100101 to 20140531. same with date. basically i want to to al the sales til the date/month /year selected. how can i achive this?

    Thanks in advance


    prajwal kumar potula

    Thursday, August 20, 2015 3:40 PM

Answers

  • Hi Prajwal

    you can use the sum function in MDX together with range

    CREATE MEMBER CURRENTCUBE.MEASURES.SalesToDate AS 
        SUM(NULL: [Date].[Calendar].CURRENTMEMBER,([Measures].[Sales Amount]));

    this will sum up all the sales up untill the current member.

    • Marked as answer by Prajwal Potula Thursday, August 20, 2015 7:46 PM
    Thursday, August 20, 2015 4:07 PM

All replies

  • Hi Prajwal

    you can use the sum function in MDX together with range

    CREATE MEMBER CURRENTCUBE.MEASURES.SalesToDate AS 
        SUM(NULL: [Date].[Calendar].CURRENTMEMBER,([Measures].[Sales Amount]));

    this will sum up all the sales up untill the current member.

    • Marked as answer by Prajwal Potula Thursday, August 20, 2015 7:46 PM
    Thursday, August 20, 2015 4:07 PM
  • Thanks Michael, 

    Just found the same online. it's pretty cool. I have one more question. in the below query, IF condition, how do i say go with calendar hierarchy if i have calendar hierarchy on rows and go with week hierarchy if i have week hierarchy in my select.

    WITH 
    MEMBER SS AS [Date].[Calendar Hierarchy].CURRENTMEMBER.LEVEL.ORDINAL 

    MEMBER ABC AS 
    IIF( [Date].[Calendar Hierarchy].CURRENTMEMBER.LEVEL.ORDINAL <> 0,
    SUM({NULL: [Date].[Calendar Hierarchy].CURRENTMEMBER},[Measures].[Distinct Account Count]),
    SUM({NULL: [Date].[Calendar Week Hierarchy].CURRENTMEMBER},[Measures].[Distinct Account Count]))

    SELECT NON EMPTY {SS} ON 0,
    NON EMPTY ([Date].[Calendar Week Hierarchy].[Calendar Week].&[201402]) ON 1
    FROM GPR


    prajwal kumar potula

    Thursday, August 20, 2015 7:45 PM