Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

MDX Date ranges Expand / Collapse
Author
Message
Posted Monday, August 24, 2009 9:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 04, 2013 8:02 AM
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
Post #776119
Posted Monday, August 24, 2009 6:42 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 03, 2013 8:24 AM
Points: 1,240, Visits: 5,421
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
Post #776393
Posted Tuesday, August 25, 2009 7:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 04, 2013 8:02 AM
Points: 7, Visits: 105
Tip top solution and quick to.

Thanks for that
Post #776682
Posted Thursday, December 13, 2012 7:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 24, 2013 6:40 AM
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
Post #1396184
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse