SQL Server Performance Forum – Threads Archive

Urgent : MDX Query to find next X-values

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.

---

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

---

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.

---

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

---

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

---

]]>