asked on

How do I create date dimension(s) so the output is ytd, mtd, dtd?

Newby here,

I need, mostly step by step, in configuration a date dimension that will provide year to date, month to date or day to date.

I would think this is a common occurrence but I have not been successful in finding this.

What you say? 500 points 4 u.

stalhw

What do you mean by date dimension?
you mean a where clause?
it can be done in multiple ways, using datepart or datediff, or BETWEEN...

Here's some examples:
1- Year to date
WHERE DATEPART(yyyy,datefield)=DATEPART(yyyy,getdate())
2- Month to date
WHERE DATEDIFF(mm,0,datefield)=DATEDIFF(mm,0,getdate())
3- Day to date
WHERE DATEDIFF(dd,0,datefield)=DATEDIFF(dd,0,getdate())

rhservan

ASKER

In building my analysis cube I currently am using a date dimension of which I built 4 hierarchies:

The date dimension has columns whcih reflect Calendar \ Fiscal colums such as fiscal week , calendar year etc........

Hierarchy Example:
FY-FQ-FM-FW-FD

These are working fine as a drill through from FY thru FD.

What I need now is to structure a hierarchy which will give me FYTD functionality. This would be represented as For an example:
Fiscal Year begins in October and it is now November, I need:
1. year to date accumuitve
October total is 100 November (to date) is 72 YTD would = 172

ASKER CERTIFIED SOLUTION

stalhw

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rhservan

ASKER

@stalhw - Thx, though. Tell one of your buddies that may know I will split points.