August 24, 2009 at 9:52 am
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
August 24, 2009 at 6:42 pm
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
August 25, 2009 at 7:44 am
Tip top solution and quick to.
Thanks for that 🙂
December 13, 2012 at 7:25 am
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply