I need to create dynamic to-date measures within a universe.
These measures need to be dynamic within the time dimension. The time dimension includes week, month,quarter, season, and year. measures include sales, MTD sales, QTD sales, STD sales… The user needs the ability to create an ad-hoc report with any combination of these objects.
I have been through the threads regarding to-date measures and have tried many of the suggested solutions. Where I’m running into a problem is when multiples of the time dimension objects are used. I can create a measure that will work at the week level and another that will work at the month level, but not a single measure that will handle both.
I could use aggregate tables at each time dimension level, but creating those tables will add multiple terabytes of data to the warehouse.
My guess is that you’ll want to create a FILTER object that is used to determine if you want YTD, QTD or MTD. You can then use a single “measure” object, with your filter to determine which of the aggregation levels to use.
You will have to run mulitple queries to get aggregation at more than one level. A better way to build this (to have all three objects in the same report without requiring multiple runs) is to build three distinct measures using a case statement to determine if the value you are summarizing needs to be included for time frame xxxx.
I can’t seem to figure out a way to create a single MTD Sales measure that will work when summed at the week level and at the month level.
I also forgot that the time dimension is not calendar, it is fiscal, which makes it harder to use a case statement within the select area of the object.
Fiscal or calendar shouldn’t matter, as long as you have a way to convert from one to the other (or use a calendar table which you can join to). If it’s a simple month-shift then you can still handle this in the object builds.
Anyway… you shuold be able to create formulas that generate your values for you (of course you’ll have to shift to deal with fiscal vs. physical calendar dates).
MTD:
sum( CASE WHEN trunc( dateobj, 'MM') = trunc( sysdate, 'MM') then DollarObj else 0 end )
QTD:
sum( CASE WHEN trunc( dateobj, 'Q') = trunc( sysdate, 'Q') then DollarObj else 0 end )
YTD:
sum( CASE WHEN trunc( dateobj, 'YYYY') = trunc( sysdate, 'YYYY') then DollarObj else 0 end )
Thanks Anita for the reply. But my question here is KEY POINT is I am not using DATE FIELD at all, I am only basing on Fiscal YR and Fiscal Month Fields On the Report I want to prompt Fiscal YR and MOnth and so from that Month in the Fiscal YR to the Start of that Fiscal Yr I need data to be shown on the report.
Say Fiscal yr - user selected = 2006
Fiscal Month - May
I should be able to get the values from July 2005-May 2006
Assuming you have a fiscal calendar table that defines your fiscal months/year etc., and it is appropriately linked into your fact tables; you could simply add a month rank field to this table that denotes month 1 in your fiscal calendar to month 12. (We’ve ranked months over multiple years, so that you can easily do rolling history 12 month history.) Then convert the users prompted month selection into the month number (or simply have the users select fiscal month #). Your prompts will be: Year equal to “Year Prompt”, and Fiscal Month less than or equal to “Month Prompt”.
(BOB member since 2005-09-14)
(BOB member since 2005-05-23)