SQL Server Performance

Urgent : MDX Query to find next X-values

Discussion in 'SQL Server 2005 Analysis Services' started by sathyanath, May 31, 2006.

  1. sathyanath New Member

    Hi,
    Im facing a problem while trying to get the sum total of days of month.
    (ie) Jan 1 to 31st have its own sales amount.I've write a MDX query where it has to show the sum of date 2 to 31st in 1st Note the current sales amount shouldnot get added only the next all dates for that particular month,where 31 should show null b'coz next date will be the 1st of Feb...I need to get the result for all month.

    Thanks.
  2. ranjitjain New Member

    Create the set of all dates using range operator or look for prev member, children or descendants based on the hierarchy you have designed.
    Then take the sum of measure over that set
  3. sathyanath New Member

    Thanks Ranjith,
    I tried hierarchey method but its not working im getting the sum if hole month.I need the sum of hole month except the surrentdate.
    sales amount Result
    (ex) 1.1.2001 - 10 50
    2.1.2001 - 20 30
    3.1.2001 - 30 next total till 31st
    31.1.2001 - 0 B'coz next date will be
    1.2.2001 - 10 20
    2.2.2001 - 20 next total till last date.
    but previousmember function is = lag(1) function it goes one step previous where we need sum of next x numbers. Hope u might get the problem ? im facing.

  4. ranjitjain New Member

    Hi,
    Your expected result is still not very clear to me.
    Regarding prev member i meant to use filter function which will exclude that first member from that set of all members for that month.

    for 1.1.2001 result is 50 (how you calculated this?)
    and so on
  5. ranjitjain New Member

    check this:
    here i'm creating a set of all children for april month and then in other set excluding the 1st day of april using except.

    with set Mydates as
    [Time Dim].[Year - Month - Day ].[Month time].&[2006-04-01T00:00:00].children

    set Myactualdates as
    except({Mydates},head(Mydates,1))

    select Myactualdates on 0 from cube

Share This Page