BusinessObjects Board

Report Filter and YTD calculation

building reports webintelligence
#1

Hi All,

I have to create a report for monthly and YTD calculation in BO which will have product,month and year report filters on top of it.
For YTD calculation if user have selected Month=“FEB” then it should give min of jan+min of Feb,but i think because of report filter applied for month =“FEB”. the formula for YTD is :-

sum(min) where ([month] inlist ("jan";"feb")

Can anyone of you help me on this.

:hb:

[Moderator Edit: Added code formatting - Andreas]

himansu rai (BOB member since 2015-10-06)

#2

If you want a year to date figure, you may be better building a case statement at the universe level.

At the report level, if you’ve filtered for Month = Feb, you’ll only get Feb data

If you filter for Month <= Feb, you’ll get Jan and Feb, unless your months are strings, in which case you’ll just get Feb because J comes after F alphabetically.

#3

Hi Mark,

Thanks for your reply :slight_smile:

I was trying to create 12 diffrent variables for 12 months like :

v_Jan=where month=“jan”
v_feb=where month=“jan” or month=“Feb”
.
.
.
V_dec=where month=“jan” or month=“Feb” or . . . . month=“Dec”

Now i am creating another variable monthly YTD which will have given formula

If month=“jan” then v_jan
elseif month=“Feb” then v_feb
.
.
.
.
elseif month=“Dec” then v_dec

so,now instead of applying filter on month, user will apply filter on month_ytd.

lets say if user have selected month_ytd= feb then he should get records for jan and feb.

but unfortunately above idea is not working. :hb:

please correct if i am missing something :frowning:

himansu rai (BOB member since 2015-10-06)

#4

What sort of filter are you trying to create, one that goes in the filter bar?
Or an input control?

#5

user requirement is for Filter bar :?:

thanks again :slight_smile:

himansu rai (BOB member since 2015-10-06)

#6

Haven’t you got month numbers in your universe?

MBLM :netherlands: (BOB member since 2014-01-31)

#7

Thanks MBLM for your reply :slight_smile:

could you please explain little more about month numbers.

I am sure that i don’t have any object like this.

please confirm is there any way to achieve this in WEBI itself.

:frowning:

himansu rai (BOB member since 2015-10-06)

#8

The far simpler solution is to use an input control - use the slider display option and less than or equal to for your months

#9

Hi Mark,

what could be the best possible way to implement the YTD logic in universe itself ?

Thanks in advance :smiley:

himansu rai (BOB member since 2015-10-06)

#10

Using case expressions.

YTD on SQL Server:

SUM
( CASE WHEN sales_calendar.sales_date BETWEEN @Select(Relative Dates\Start of Current Year) AND @Select(Relative Dates\Yesterday)
   THEN sales_fact.sales_value
   END
 )

This assumes that:
[list]You have a calendar table in your database and have created an alias of it called sales_calendar which is joined to your sales_fact table and,
You have set up a relative dates class in your universe, as per this thread[/list]

[Moderator Edit: Added code formatting - Andreas]