Solved

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

Posted on 2011-09-06
4
262 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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

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…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now