| Author |
Topic |
|
Joozh
Posting Yak Master
145 Posts |
 Posted - 2004-08-04 : 06:49:57
|
Hi,I'm really frustrated becuase it appears that Microsoft Data Analyzer's support/compatability with "standard" MDX is pathetic  I need to calculate the following but just can't figure out the "~Aspect ... " logic which Data Analyzer uses:1) I want to create a Year to Date total of Sales. Regardless of the selected time period (whether it's Jan or Nov), I want this YTD total to return the total sales for the selected year2) I want to calculate the total sales in the previous time period i.e. let me try to explain in detail- if the selected time period is Year, this should return the total sales of the Previous Year
- if the selected period is Qtr1 of a year, this should return the total sales of the previous period i.e. Qtr 4 of the previous year
- if the selected period is Qtr 2 it should return the total sales for Qtr 1
- if the selected period is say Oct of 2004, it should return the total sales of Sep 2004
Can someone PLEASE help me with these. Many & Billions of thanks. |
|
|
griva
Starting Member
7 Posts |
Posted - 2004-08-12 : 13:44:18
|
| Q#1: You can use the ClosingPeriod function - it returns the value for the period you specify as an argument.As to the Q #2 - use PrevMember functionSomething like this: [D_TIME].Currentmember.PrevMember |
 |
|
|
Joozh
Posting Yak Master
145 Posts |
Posted - 2004-08-13 : 01:48:20
|
| grive thanks. This provides some clue. Let me try with your suggestion and hopefully ... :)Many thanks for your reply.Thanks & Regards.-J |
|
|
|
griva
Starting Member
7 Posts |
Posted - 2004-08-13 : 11:23:54
|
| Let me know if you need help -I'll try to elaborate |
|
|
|
Joozh
Posting Yak Master
145 Posts |
Posted - 2004-08-23 : 07:27:06
|
| Hi griva,I think i can use some help.... I have tried your suggestion and am sure that I am doing everthing/something wrong and therefore am not getting the desired results.Sorry for bothering but can you please guide me step by step (if possible) regarding how to do this?Many many thanks in advance.Thanks & Regards.-J |
|
|
|
Joozh
Posting Yak Master
145 Posts |
Posted - 2004-08-24 : 00:56:39
|
quote:
Joozh , Let me understand it correctly - do you want the total from the end of the year, or do you want the total from whatever period of the year we are at the moment - in other words, if it is August, and they pick March, you want the value for August or for December (which will be empty of course!) thanks
Hi griva,Many thanks for your reply :)Let me try to explain it. I hope this will clrealy explain my requirement.First I'll try to explain my requirement #1 i.e. The Year To Date (YTD) total:Suppose the current date is 24-Aug-2004. Regardless of which quarter/month etc. the user selects, I want the YTD column to display the year to date total sales i.e. from 01-Jan-2004 till 24-Aug-2004Now let me try to explain my 2nd requirement which probably is a little more tricky i.e. the 'Previous Period Sales'- Suppose the user has selected just the year 2004. At the year level the previous period is ofcourse the last year and thus in the 'Previous Period Sales' I want the total sales for 2003
- Suppose the user has selected Quarter 1 of 2004. The previous period now in this case would be Quarter 4 of 2003 and thus in the 'Previous Period Sales' I want the total sales for Quarter 4 of 2003
- Suppose the user has selected Quarter 2 of 2004. The previous period now in this case would be Quarter 1 of 2004 and thus in the 'Previous Period Sales' I want the total sales for Quarter 1 of 2004
- Suppose the user has selected Feburary 2004. The previous period now in this case would be January 2004 and thus in the 'Previous Period Sales' I want the total sales for January 2004
- and so on....
Does this explain my requirement a litle more clearly? If not please let me know and I can add more details/examples...Many thanks and eagerly looking for a solution :)Thanks & Regards.-J |
|
|
|
griva
Starting Member
7 Posts |
Posted - 2004-08-24 : 16:31:21
|
| And here I am sitting and checking my e-mail :-)Ok, #1 - if you want the ytd amount then I would do the following:a) create a new calculated member.b) use the following code - you need to change all dimensions' names - if your time dimension is called differently - change D_TIME to whatever name you have.Same for the word Sales - it should be the name of your measure you want to see the ytd amountiif([D_TIME].currentmember.level.name = "Year",([D_TIME].currentmember,[Measures].[Sales]),0)#2 - I will post about it tomorrow - got to run |
|
|
|
griva
Starting Member
7 Posts |
Posted - 2004-08-25 : 08:28:10
|
| #2 New calculated member And the MDX: (ParallelPeriod(),[Measures].[Sales])ParallelPeriod() The parallel period in the immediately prior sibling to the parent of Time.CurrentMember. For example, if Time.CurrentMember is [1993June], the returned member is [1993March]. The parent of ([1993June] is Quarter2, whose immediately prior sibling is Quarter1, in which the parallel period is [1993March]. |
|
|
|
Joozh
Posting Yak Master
145 Posts |
Posted - 2004-08-26 : 00:59:58
|
| Your are simply GREAT!!!! :)Will try it out ASAP.Many thanks griva.Thanks & Regards.-J |
|
|
|
|
Need MDX help (in Microsoft Data Analyzer)