Universe Monthly Measure based on two dates

[Former Member]

Hi All,

Is it possible to create point of time metric repeated by month in BO universe which is based on two date conditions.

I have a Measure in universe to calculate beginning of the month(BOM) sales based on the condition : Sum(Sales ) where startdate < 10/1/2013 and enddate >=1/10/2013 or enddate is null . This will give me BOM for OCT2013.

I am just wondering is there any way in BOBJ universe to creat a BOM measure that calculates  BOM counts by month ? Current measure that I have calculates BOM for a selected date/ period only,  meaning if I need to find BOM for AUG,SEP and OCT of 2013 I need to execute the measure separately once for each  values (LOV) (8/1/2013;9/1/2013;10/1/2013). It will not be calculated for a range  like monthly or weekly sales measure based on single date where I can select a date  range and use calendar table for roll up.

I do have a calendar table and data in my fact is at daily grain.

Thank you !

[former_member207878]

Try to use below formula at report level:

=Sum(Sales ) Where (DayNumberOfMonth([Date])=1)

And if you want to get first of the month at Universe level, you can use below:

trunc(@Select(’ClassName\DateObject’),’MM’)

It will return you first day of that month.

[Former Member]

Shail,

Thank you for the response. My requirement is not to find the value for a specific period its for a range and for specific time period BOM above,  based on two dates.

Thanks,

Raja

[former_member207878]

Can you please provide some example with data, so we will come to know what you want exactly?

Thanks!