January 14, 2011 at 8:54 am
How do i write calculation for 12 month period starting which is February of every year to January of next year.
i have time table which has three years of data by year, quarter, month..
How can i achieve this in a calculated column..
12 month period
2008 feb, march, apr, may, jun, jul, aug, sep, oct, nov,dec, 2009 jan.
Thanks
Sid
January 17, 2011 at 9:09 am
If you are using SQL server 2005/8 then take a look at the Adventure works cube to get the general Idea as that has a very good example in the Finance perspective.
Basically you add another column to the Date dimension which allows you to group the dates differently.
for example in a finance cube, You have Calendar Year which runs Jan-Dec, and a Fiscal Year that might run Apr-Mar.
It all depends how rigid your design is and if you can change the underlying database.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
January 17, 2011 at 9:36 am
but i have single table, which has three columns, year, quarter month and period key column.. i have to create a new table in database
as you said 1) fiscal year table 2) calendar year table)
cant we do from one table...
sid
January 17, 2011 at 10:41 am
Not sure Jason said you had to create a new table.... Just work with the different calendars (hierarchies) in that Dim.
Take a look at the official documentation here, it runs through these.
Steve.
January 17, 2011 at 10:50 am
but cant i do filter in my dimension, saying current year of feburary to next year of jan, using mdx..
January 17, 2011 at 11:29 am
Yes, you can definitely do that - thought you were looking to add the (alt) calendar permanently to the dim.
Simplest way is to simply define a range, like (depending on how your keys are set up) -
[MyCalendar].[2010_Feb]:[MyCalendar].[2011_Jan]
You can of course get more sophisticated and use some functions to determine what should be the starting period of this range based on other data factors.
Steve.
January 17, 2011 at 12:38 pm
If you dont want to alter the table then steves solution is the optimal one, however, you will most likely lose drill down functionality it depends on the business requirements you have.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
January 18, 2011 at 9:30 am
i will try playing with time dimension wizard and i will post the result..
thanks guys
sid
January 18, 2011 at 11:18 am
I played with fiscal year option , i selected start date as Feb 1 2008 and end date as jan 1 2010.
after procesing dimension and cube. I selected heirarchy as Year, Quarter, Month.
2008, q1, feb ,mar, apr
q2 :may,jun,jul
q3:aug,sep,oct
Q4:nov dec jan 1 2008..
jan of 2008 is displayed in q4. but suppose to show jan 2009 in q4..
fiscal year option didnt work..
January 18, 2011 at 11:35 am
Seems very strange that it would work for 11 out of 12 months..... There's nothing else at play here? bad labels? keys? specified ranges? seems strange that you would specify Jan 01 2010 as the start end date? Surely it's 31st jan 2010?
Steve.
January 18, 2011 at 12:25 pm
Here's table structure and data
may be calculation works based on month
calculation1 as get month of feb to dec of year
calculation 2 as get january month of next year
merging both calculations..
i'm attaching excel sheet of our time table.
January 19, 2011 at 2:18 am
I think I see whats happening, and I hope the following makes sense.
In financial terms if the year is split across years eg Apr 2008-Mar 2008, it is generally refered to as 2008/9, and the Preceeding year tends to take precedence.
So in your example of Feb-Jan, the months in the current Year show as correct, but Jan doesnt is simply a labeling issue, It really is Jan 2009.
Firstly I would check the Length of the Fiscal Year attribute if its a WSTR 4, then increase it to 6 and see if that adds anything to the dim.
If that doesnt work may need to add a fiscal year column to the Calendar table, and use that as the year in the Fiscal year Dimension. This could be done as a Named query in the DSV, tosave changeing the DB.
I would test the data first, by looking at the data for Jan 2009, using the standard Date Hierarchy, write down the value against it, then switch into the Fiscal year Hierarchy, and look at the Number against Jan 2008 in Q4.
If they are the same then its simply the way SSAS is simply rolling the preceeding year forward and displaying jan 2008 as jan 2009.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
January 20, 2011 at 10:20 am
i added another fiscal year column in same table . it worked..
thanks..
sid:-)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply