Data Warehousing and Business Intelligence

19 December 2011

Half Year To Date

Filed under: Analysis Services — Vincent Rainardi @ 7:50 am

Half Year To Date

In MDX we have Year To Date, Month To Date and Week To Date functions, but we don’t have Half Year To Date. This article is about how to do Half Year To Date.

YTD (Year To Date)

We use the YTD function to can get all dates/months from 1st Jan to a certain date. For example:

with member x as
( Aggregate
  ( YTD
    ( [Dim Date].[YMD Hierarchy].[Date].[2011-03-05]
    ), [Measures].[Measure1]
  )
)
select x on columns
from Cube;

The above MDX sums up measure1 from 1/1/2011 to 5/3/2011.

Note: in order for YTD to work,

  1. The type of the date dim must be “time”.
  2. The type of the year attribute must be “years”.

Another example:

with member x as
( Aggregate
  ( YTD
    ( [Dim Date].[YMD Hierarchy].[Month].[201103]
    ), [Measures].[Measure1]
  )
)
select x on columns
from Cube;

The above MDX sums measure1 from 1st Jan to end of March 2011.

with member x as
Aggregate
( YTD
  ( [Dim Date].[YHMD].CurrentMember
  ), [Measures].[Measure1]
)
select x on columns,
[Dim Date].[YHMD Hierarchy].[Date] on rows
from Cube;

The above lists all the dates and the sum of measure1 from 1st Jan to that date.

MTD (Month To Date) and WTD (Week To Date)

We use MTD function to get all the dates from the beginning of the month to a particular date. For example:

with member x as
( Aggregate
  ( MTD
    ( [Dim Date].[YHMD].[Date].[2011-03-01]
    ), [Measures].[Measure1]
  )
)
select x on columns
from Cube;

The type of the “month” attribute must be “months” otherwise MTD won’t work.

WTD works the same way as YTD and MTD, i.e. we get all the dates from the beginning of the week to the date we specified. Again the type of the “week” attribute must be “weeks”.

Half Year to Date (HTYD)

To do HYTD we can do this:

with member x as
Aggregate
(
  ( Ancestor
    ( [Dim Date].[YHMD Hierarchy].CurrentMember,
      [Dim Date].[YHMD Hierarchy].[Half Year]
    ).FirstChild.FirstChild:
    [Dim Date].[YHMD Hierarchy].[Date].[2011-08-17]
  ), [Measures].[Measure1])
select x on columns
from Cube;

This code sums measure1 from 1/7/2011 to 17/8/2011.

Here is how it works: from 17/8/2011 we climb up 2 levels to get the half year which is 2011H2. Then we get the first child of 2011H2 which is July 2011. Then we get the first child of July 2011 which is 1/7/2011. We then sum measure1 from 1/7/2011 to 17/8/2011.

Of course in order for this to work you must have Half Year attribute in your date dimension. The type of this Half Year attribute doesn’t have to be “half year”.

Another way of doing HTYD

If you don’t have Half Year level on your date dimension, you can you HYTD like this:

with member a as membertostr([Dim Date].[Date].CurrentMember)
member b as len(a) member c as left(a,25) member d as mid(a,28,100)
member e1 as c+"01"+d member e2 as c+"07"+d
member x as
iif
( mid([Dim Date].[Date].CurrentMember.Name,6,2) <= "06",
  Aggregate
  ( ( strtomember(e1):[Dim Date].[Date].CurrentMember ),
    [Measures].[Measure1]
  ),
  Aggregate
  ( ( strtomember(e2):[Dim Date].[Date].CurrentMember ),
    [Measures].[Measure1]
  )
)
select x on columns,
[Dim Date].[Date].[Date] on rows
from Cube;

Here is how it works: first we convert the current member to string, i.e. for 17/8/2011 we get [Dim Date].[Date].&[2011-08-17T00:00:00]

0        1         2         3         4
1234567890123456789012345678901234567890
[Dim Date].[Date].&[2011-08-17T00:00:00]

We then get the first 25 characters: “[Dim Date].[Date].&[2011-“ —– (c)

Then get character 31 onwards: “T00:00:00]” —– (d)

And we replace the month and date with 01-01 and 07-01 which are the beginning of the half year:

[Dim Date].[Date].&[2011-01-01T00:00:00] —– e1

[Dim Date].[Date].&[2011-07-01T00:00:00] —– e2

Now we can sum measure1 from the beginning of half year to the current member:

(strtomember(e1):[Dim Date].[Date].CurrentMember), or

(strtomember(e2):[Dim Date].[Date].CurrentMember)

But first we need to find out if the current member is on H1 or H2:

iif( mid([Dim Date].[Date].CurrentMember.Name,6,2) <= “06”, …

Find Function

If you don’t like hardcoding the “25” and “31” above you can use the “instr” function to find string within a string in MDX:

with member a as
instr(membertostr([Dim Date].[Date].CurrentMember),'T')
select a on columns,
  [Dim Date].[Date].[Date] on rows
from Cube;

Scratch Page

Below is my scratch page when doing the work. I’ll share it as some of you might find it useful for doing string parsing and date parsing in MDX.

select [Measures].[Measure1] on columns,
[Dim Date].[Date].[Date] on rows
from HYTD;

with member x as
Aggregate
( ([Dim Date].[Date].FirstChild:[Dim Date].[Date].CurrentMember),
[Measures].[Measure1])
select x on columns,
[Dim Date].[Date].[Date] on rows
from HYTD;

with member x as
iif(1=1, Aggregate
( ([Dim Date].[Date].FirstChild:[Dim Date].[Date].CurrentMember),
[Measures].[Measure1]), 2 )
select x on columns,
[Dim Date].[Date].[Date] on rows
from HYTD;

with member x as
iif([Dim Date].[Date].CurrentMember.Name = "2011-01-01", 1, 2 )
select x on columns,
[Dim Date].[Date].[Date] on rows
from HYTD;

with member x as
iif(mid([Dim Date].[Date].CurrentMember.Name,6,2) <= "06", 1, 2 )
select x on columns,
[Dim Date].[Date].[Date] on rows
from HYTD;

with member x as
iif
( mid([Dim Date].[Date].CurrentMember.Name,6,2) <= "06",
Aggregate
( ([Dim Date].[Date].FirstChild:[Dim Date].[Date].CurrentMember),
[Measures].[Measure1]
),
2
)
select x on columns,
[Dim Date].[Date].[Date] on rows
from HYTD;

select {} on columns,
[Dim Date].[Date].[Date] on rows
from HYTD;

with member x as
len(membertostr([Dim Date].[Date].CurrentMember))
select x on columns,
[Dim Date].[Date].[Date] on rows
from HYTD;

with member x as
len(membertostr([Dim Date].[Date].CurrentMember))
select x on columns,
[Dim Date].[Date].[Date] on rows
from HYTD;

with member x as
membertostr([Dim Date].[Date].CurrentMember)
member y as len(x)
select x on columns,
[Dim Date].[Date].[Date] on rows
from HYTD;

with member a as
membertostr([Dim Date].[Date].CurrentMember)
member b as len(a)
member c as left(a,25)
member d as mid(a,28,100)
member e as c+"06"+d
select e on columns,
[Dim Date].[Date].[Date] on rows
from HYTD;

with member a as
membertostr([Dim Date].[Date].CurrentMember)
member b as len(a)
member c as left(a,25)
member d as mid(a,28,100)
member e as c+"06"+d
member f as strtomember('[Dim Date].[Date].&[2011-01-01T00:00:00]')
select {strtomember('[Dim Date].[Date].&[2011-01-01T00:00:00]')} on columns,
[Measures].[Measure1] on rows
from HYTD;

with member a as
membertostr([Dim Date].[Date].CurrentMember)
member b as len(a)
member c as left(a,25)
member d as mid(a,28,100)
member e as c+"06"+d
member f as strtomember(e)
select {strtomember('[Dim Date].[Date].&[2011-06-01T00:00:00]')} on columns,
[Measures].[Measure1] on rows
from HYTD;

with member x as
Aggregate
(
( Ancestor ( [Dim Date].[YHMD].CurrentMember, [Dim Date].[YHMD].[Half Year] ).FirstChild.FirstChild:
[Dim Date].[YHMD].CurrentMember
),
[Measures].[Measure1])
select x on columns,
[Dim Date].[YHMD].[Date] on rows
from HYTD;

with member x as 1
select x on columns,
[Dim Date].[YHMD].[Date] on rows
from HYTD;

with member x as
membertostr
( Ancestor ( [Dim Date].[YHMD].CurrentMember, [Dim Date].[YHMD].[Half Year] ).FirstChild.FirstChild)
select x on columns,
[Dim Date].[YHMD].[Date] on rows
from HYTD;

with member x as
Aggregate
(
( Ancestor
( [Dim Date].[YHMD].CurrentMember,
[Dim Date].[YHMD].[Half Year]
).FirstChild.FirstChild:
[Dim Date].[YHMD].[Date].[2011-03-01]
),
[Measures].[Measure1])
select x on columns
from HYTD;

with member x as
Aggregate
(
( Ancestor
( [Dim Date].[YHMD].CurrentMember,
[Dim Date].[YHMD].[Half Year]
).FirstChild.FirstChild:
[Dim Date].[YHMD].CurrentMember
),
[Measures].[Measure1])
select x on columns,
[Dim Date].[YHMD].[Date] on rows
from HYTD;

with member x as
(Aggregate(YTD([Dim Date].[YHMD].[Date].[2011-03-01]), [Measures].[Measure1]))
select x on columns
from HYTD;

with member x as
Aggregate(YTD([Dim Date].[YHMD].[Date].[2011-03-01]), [Measures].[Measure1])
select x on columns,
[Dim Date].[YHMD].[Date] on rows
from HYTD;

with member x as
Aggregate(YTD([Dim Date].[YHMD].CurrentMember), [Measures].[Measure1])
select x on columns,
[Dim Date].[YHMD].[Date] on rows
from HYTD;

with member x as
(Aggregate(YTD([Dim Date].[YHMD].[Month].[201103]), [Measures].[Measure1]))
select x on columns
from HYTD;

with member x as
(Aggregate(MTD([Dim Date].[YHMD].[Date].[2011-03-01]), [Measures].[Measure1]))
select x on columns
from HYTD;

with member x as
(Aggregate(WTD([Dim Date].[YHMD].[Date].[2011-03-01]), [Measures].[Measure1]))
select x on columns
from HYTD;

-- If you don't have HY level
with member a as membertostr([Dim Date].[Date].CurrentMember)
member b as len(a) member c as left(a,25) member d as mid(a,31,100)
member e1 as c+"01-01"+d member e2 as c+"07-01"+d
member x as
iif
( mid([Dim Date].[Date].CurrentMember.Name,6,2) <= "06",
Aggregate
( (strtomember(e1):[Dim Date].[Date].CurrentMember),
[Measures].[Measure1]
),
Aggregate
( (strtomember(e2):[Dim Date].[Date].CurrentMember),
[Measures].[Measure1]
)
)
select x on columns,
[Dim Date].[Date].[Date] on rows
from HYTD;

with member a as
membertostr([Dim Date].[Date].CurrentMember)
member b as len(a)
member c as left(a,25)
member d as mid(a,31,100)
member e as c+"07-01"+d
select e on columns,
[Dim Date].[Date].[Date] on rows
from HYTD;

--[Dim Date].[Date].&[2010-06-01T00:00:00]

-- research find function
with member a as
instr(membertostr([Dim Date].[Date].CurrentMember),'T')
select a on columns,
[Dim Date].[Date].[Date] on rows
from HYTD;

And here are the SSAS project (link) and the SQL table DDL+population script (link).

Vincent Rainardi 19/12/2011

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: