Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


MDX Date ranges


MDX Date ranges

Author
Message
Intrepid121
Intrepid121
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 105
Hi,

I've got a query that i'm working on that's giving me a sore head.

I need to display the first 3 years at month level, the next 10 years at Quarter level, and the next 10 at year level, but i can't seem to get it happening.

Any help much appreciated

Thanks,

J
drew.allen
drew.allen
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2687 Visits: 9960
I used AdventureworksDW, which only has four fiscal years of data, so I had to cheat on some of the numbers, but here is what I did. There may be a better way, but this way works.

I used the Head function to pull off the first one (but you will want three) years of the children in the FY Hierarchy. Then create another set by generating the descendants of that first set at the month level.

Then I repeated the process except excluding the set of years that I had already processed.


WITH
SET Set1 AS
Head([Due Date].[FY Hierarchy].Children, 1)

SET Months AS
Generate(
Set1
, Descendants( [Due Date].[FY Hierarchy].CurrentMember, 2)
)

SET Set2 AS
Head(
Except(
[Due Date].[FY Hierarchy].Children
, Set1
)
, 2
)

SET Quarters AS
Generate(
Set2
, Descendants( [Due Date].[FY Hierarchy].CurrentMember, 1)
)

SET Years AS
Head(
Except(
[Due Date].[FY Hierarchy].Children
, {Set1, Set2}
)
, 1
)

SET Details AS
{Months, Quarters, Years}

SELECT [Measures].[Sales Amount - Fact Reseller Sales] ON Columns
, [Due Date].[Fiscal Year].Children * Details ON Rows
FROM [Adventure Works DW]



(I didn't like the FY Hierarchy level names, so I used the level numbers instead. If this had been a cube that I had created from scratch, I would have had better level names and used the names.)

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
Intrepid121
Intrepid121
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 105
Tip top solution and quick to.

Thanks for that :-)
swapp
swapp
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 17
Hi ,

Can anyone help me out writing MDX for finding products wich are present for current date but not in previous date. Below is SQL query for the same.

select count(distinct Product) from table_A
where Date='2012-12-12'
and Product not in (select distinct Product from table_A
where Date='2012-12-01')

thanks in advance...
----Swapnil
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search