dbTalk Databases Forums  

PeriodsToDate in a flat time dimension

microsoft.public.sqlserver.olap microsoft.public.sqlserver.olap


Discuss PeriodsToDate in a flat time dimension in the microsoft.public.sqlserver.olap forum.



Reply
 
Thread Tools Display Modes
  #1  
Old   
davidb@mercurydata.com
 
Posts: n/a

Default PeriodsToDate in a flat time dimension - 04-08-2005 , 07:18 AM






My time dimension is a flat month dimension because my measure is not
additive across months. And still, I want a YTD measure. I'll explain
the details of my month dimension and describe what I've attempted.
Please help me refine my approach or suggest a new one. MANY THANKS!

Actually, my need is a little more specific than a YTD summary. Because
the company's fiscal year starts in April, my flat list of months in
the dim is

Jan FY04
Feb FY04
Mar FY04
Apr FY05
May FY05
Jun FY05
Jul FY05
.... and so on.

I want a calculated measure that will sum from [Apr FY05] to the
present month. We don't have data before [Jan FY04] so I'd rather just
leave the calculated measure = 0 for those months. Here's what I've
tried so far:

IIf(
<Condition if month < Apr FY05>,
0,
Sum([Month].[Apr FY05]:[Month].CurrentMember , [Measure]
)

I cannot come up with a good condition that identifies the months
previous to my beginning month. If feel silly about that . I also
wonder if there is another function that will do a better job here
rather than rolling my own YTD function.


Reply With Quote
  #2  
Old   
OLAPMonkey
 
Posts: n/a

Default Re: PeriodsToDate in a flat time dimension - 04-08-2005 , 11:00 AM






Put in a year level...if you don't want to display values at that
level...then just add in a custom rollup that makes the year level
always show Null. Then you can use the built in YTD formula as long
as you've marked your dimension as a "Time" type dimension and your
year and month levels as "Year" type and "Month" type respectively.


Reply With Quote
  #3  
Old   
davidb@mercurydata.com
 
Posts: n/a

Default Re: PeriodsToDate in a flat time dimension - 04-08-2005 , 01:58 PM



This worked very well. Thanks!!


Reply With Quote
Reply




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.