time relatives - YTD, MTD, QTD the best approach

View previous topic View next topic Go down

time relatives - YTD, MTD, QTD the best approach

Post  djoshi on Wed Jun 30, 2010 11:15 pm

What are the various design options for calculating fact by YTD,MTD, QTD for multiple different calendars?

djoshi

Posts : 1
Join date : 2010-06-30

View user profile

Back to top Go down

YTD etc

Post  kap on Mon Jul 05, 2010 7:29 am

I use Microsoft SQL Server 2005 and calculate them in the cube definition by using the Add business inteligence wizard in the Cube designer. A table level and donīt have anything like this since all my queries are using the cubes.

kap

Posts : 4
Join date : 2010-07-05

View user profile

Back to top Go down

Re: time relatives - YTD, MTD, QTD the best approach

Post  ngalemmo on Tue Jul 06, 2010 11:07 am

Can you elaborate on 'multiple different calendars'? How many are there and does it affect the interpretation of month, quarter, and year?
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: time relatives - YTD, MTD, QTD the best approach

Post  Guest on Fri Jul 16, 2010 6:02 am

We have defined a number of flags against our calendar table e.g. week to date , month to date. We have a scheduled script that runs at 1 minute past minute to update these flags every day.

To then sum up a fact becomes a simple condition - where week_to_date_flag = 'Y'.

If you have different calendars you could add and maintain flags on the calendar table - for example, we maintain calendar week,month, year to date flags and financial week, month, year to date flags on ours.

Guest
Guest


Back to top Go down

Re: time relatives - YTD, MTD, QTD the best approach

Post  ngalemmo on Fri Jul 16, 2010 12:11 pm

I have done something similar using sequence and offset values. The date dimension has sequence numbers for day, week, month, quarter and year. These are set when the table is first built (or reset when new dates are appended) by counting of each period in chronological order. There are then a series of offset values for day, week, month, quarter and yeat that are calculated daily by subtracting the current day's sequence from the date in question's sequence (all 5).

The offset values will then be zero for the current day, week, month, quarter and year, -1 for the previous period, +1 for the next future period and so on. To do quarter-to-date, for example, you would select the quarter offset = 0.

This is more flexible than flags because you can use the offsets for things like last quarter, same month last year and so on simply by selecting the correct offset. If you need to support a different fiscal calendar, you can have another set of sequences and offsets to support that.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: time relatives - YTD, MTD, QTD the best approach

Post  Guest on Mon Jul 19, 2010 10:08 am

I don't think there is much between the flags and sequences as a method. We also have flags for this week last year, this month last year, and from a user perspective I think it is more common to think in terms of yes/no that an offset value. I can see the benefit if you want to look at say two quarters ago but within our business the reporting is very much week/month/quarter/year to date and this year/last year comparisons, so flags work fine for us.

Guest
Guest


Back to top Go down

Re: time relatives - YTD, MTD, QTD the best approach

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum