Merging Time with Fact values need help

[madhancog]

Hi Gurus,
  I am in need to merge my Time with Fact values,
example
  I have sales,revenue etc in my fact tables
  I have Time dimension table
  Now in my report they need of many calculated columns like
   1.sales YTD
   2.Sales MTD
   3.Sales Previous year
but i have only year,month,date in my time dimension can any one tell me how can i achive it in Framework level.

Thanks in advance
K.M
 

[jive]

Hi metalanguage,

when we face the same problem we add column, at least 10 columns, to the time table. We prefer this solution because we can update our data anytime during the month. so to be sure we always have the right month we add column to keep the last 24 month with a key.Now we can give ytd mtd, this date minus 12 month, minus 18 minus, 24 months if we need it, and guest what they ask for it.
But if you want to used only framework manager to do that you will need a dimension and in that dimension the definition need something like month = current_month , having the current by sysdate and date in format yyyymm will do the job .

Have a nice day