Solved

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

Posted on 2011-09-06
4
271 Views
Last Modified: 2016-02-15
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.
0
Comment
Question by:rhservan
  • 2
  • 2
4 Comments
 
LVL 8

Expert Comment

by:stalhw
ID: 36490915
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())
0
 

Author Comment

by:rhservan
ID: 36491641
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
0
 
LVL 8

Accepted Solution

by:
stalhw earned 500 total points
ID: 36491667
Ok, it's cube dimensions, sorry, I haven't really worked with cubes, can't help you there.
0
 

Author Comment

by:rhservan
ID: 36491698
@stalhw - Thx, though.  Tell one of your buddies that may know I will split points.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
tempdb log contention 16 50
union query column need default text 2 15
Need return values from a stored procedure 8 21
SQL Database Restore 2008 R2 1 13
Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

837 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question