Solved

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

Posted on 2011-09-06
4
280 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

696 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