locked
MDX calculation of Daily Sales Outstanding RRS feed

  • Question

  • Hi,

    I have a financial cube and i have to calculate Daily Sales Outstanding as :

    Number of Days between the selected month last date and the earliest transaction date when cummulative sum of Revenue from last date of the month till the date where sum revenue <= the debt amount for the date .

    e.g

    On 31/12/2009 my debt amount = 2,500,000

                                                   31-Dec-09     30-Nov-09    15-Oct-09     31-Oct-09    

    Revenue                                   1,000,000     1,000,000    500,000          1,0000

    Cummulative sum of revenue     1,000,000     2,00,000     2,500,000      4,000,000

    No of Days                                    31            30                 16

    On 15/Oct/09 cummulative revenue is 2,500,000 which equals my debt amount on that day

    Count of Days = 31 + 31 + 16 = 76 Days.

    In other words Sum Revenue from the selected date backwards until sum total equals or exeeds the total to date balance of the debtors.

    Any help will be highly appreciated .

    If i haven't explained clearly enough or if you need more information then please let me know.

    Thanks in advance .

    Shuchi.


    shuchi
    Friday, November 26, 2010 10:36 AM

Answers

  • WITH MEMBER Revenue AS
    	[Measures].[Internet Sales Amount]
    MEMBER CumulativeRevenue AS
    	SUM( NULL : [Date].[Calendar].CURRENTMEMBER,
    	 [Measures].[Internet Sales Amount])
    MEMBER NoOfDays AS
    	Count(nonempty
    	(Descendants([Date].[Calendar], [Date].[Calendar].[Date]),
    	[Measures].[Internet Sales Amount]))
    MEMBER CumCountOfDays AS
    	SUM( NULL : [Date].[Calendar].CURRENTMEMBER,Count(nonempty
    	(Descendants([Date].[Calendar], [Date].[Calendar].[Date]),
    	[Measures].[Internet Sales Amount])))
    	
    SET Months AS
    {FILTER([Date].[Calendar].[Month],CumulativeRevenue<=Revenue).Item(0) : 
    [Date].[Calendar].[Month].&[2002]&[7]
    }	
    SELECT 
    {Revenue,CumulativeRevenue,NoOfDays,CumCountOfDays} on 0,
    Months on 1
    FROM 
    [Adventure Works]
    

    Not sure how far i have reached here to address your problem...I have taken AW example. 

    Internet sales amount is like the revenue.

    In AW, all are values are positive values. I guess in your case there may be negative values for revenue. As a result of all the values being positive it will fetch the data from 1st month in the hierarchy for my example as this is the only date when SUM of cumulative revenue<=revenue. It am doing item(0) because there may be several months where it is <=revenue and i get the first instance. you might have to tweak here if u want the last occurring month.

    Noofdays: Fetches count of days for which there is internet sales in the fact table. Hence a nonempty is there. 

    CumCountOfDays: fetches you Cumulative count of days

    Let me know

    Note: {I might have misunderstood: Number of Days between the selected month last date and the earliest transaction date when cummulative sum of Revenue from last date of the month till the date where sum revenue <= the debt amount for the date .}


    vinu
    • Proposed as answer by Jerry Nee Monday, November 29, 2010 7:08 AM
    • Marked as answer by shuchi Monday, November 29, 2010 9:42 AM
    Friday, November 26, 2010 2:16 PM

All replies

  • WITH MEMBER Revenue AS
    	[Measures].[Internet Sales Amount]
    MEMBER CumulativeRevenue AS
    	SUM( NULL : [Date].[Calendar].CURRENTMEMBER,
    	 [Measures].[Internet Sales Amount])
    MEMBER NoOfDays AS
    	Count(nonempty
    	(Descendants([Date].[Calendar], [Date].[Calendar].[Date]),
    	[Measures].[Internet Sales Amount]))
    MEMBER CumCountOfDays AS
    	SUM( NULL : [Date].[Calendar].CURRENTMEMBER,Count(nonempty
    	(Descendants([Date].[Calendar], [Date].[Calendar].[Date]),
    	[Measures].[Internet Sales Amount])))
    	
    SET Months AS
    {FILTER([Date].[Calendar].[Month],CumulativeRevenue<=Revenue).Item(0) : 
    [Date].[Calendar].[Month].&[2002]&[7]
    }	
    SELECT 
    {Revenue,CumulativeRevenue,NoOfDays,CumCountOfDays} on 0,
    Months on 1
    FROM 
    [Adventure Works]
    

    Not sure how far i have reached here to address your problem...I have taken AW example. 

    Internet sales amount is like the revenue.

    In AW, all are values are positive values. I guess in your case there may be negative values for revenue. As a result of all the values being positive it will fetch the data from 1st month in the hierarchy for my example as this is the only date when SUM of cumulative revenue<=revenue. It am doing item(0) because there may be several months where it is <=revenue and i get the first instance. you might have to tweak here if u want the last occurring month.

    Noofdays: Fetches count of days for which there is internet sales in the fact table. Hence a nonempty is there. 

    CumCountOfDays: fetches you Cumulative count of days

    Let me know

    Note: {I might have misunderstood: Number of Days between the selected month last date and the earliest transaction date when cummulative sum of Revenue from last date of the month till the date where sum revenue <= the debt amount for the date .}


    vinu
    • Proposed as answer by Jerry Nee Monday, November 29, 2010 7:08 AM
    • Marked as answer by shuchi Monday, November 29, 2010 9:42 AM
    Friday, November 26, 2010 2:16 PM
  • Thanks a lot.

    I applied the approach but the query is taking more than 10 min to return results This is becoz we have a huge date dimension. I will have to modify to use it on a month level only.

    Thanks again to help me think into a way to resolve this.

     

    Shuchi.


    shuchi
    Monday, November 29, 2010 9:42 AM