BusinessObjects Board

Webi 12 month rolling from current date or date in prompt

building reports
#1

Good to see this community is still alive :slight_smile:

I am struggling with a 12 month rolling request.
We run a report with data that contains posting dates.
Now we want to make the report so it shows bij material the quantity current months and the 12 months previous.
Was thinking of making 12 previous period fields every time substracting 1-12 from the month number (but of course this gets more complicated when you get into a different year).

But surely there is a more elegant solution in these newer BO versions??
(using 4.2 SP7)

Maybe I should add that we need this rolling month in Columns!
So Curr ; M-1; M-2 etc.

#2

Hi R,

Do you want to do this in Webi or create a filter in Universe? If in the Universe, what is the database?

Thanks,
Mohammed

#3

Hi Mohammed,

Only user level so in the report (webi).
But I came up with a way to make my year-range variable and then use a crosstable to always get the 12 months displayed that I need.

This is what I did.

First a variable to determine Run Date (measure):

=If (UserResponse(“Enter Posting Date”)="") Then CurrentDate() Else ToDate(UserResponse(“Enter Posting Date”);"")

Then a second variable to determine 1 year back from the run date.
Period Start (measure):

=ToDate(RelativeDate([Run Date];-1;YearPeriod);“MM/dd/yyyy”)

And then an Idicator to see if the posting date for an entry falls within this range:

=If([Posting Date]) Between([Period Start];[Run Date]) Then “Y” Else “N”

Last make a filter field like YTD_Filter:

=If([Indicator]=“N”) Then “H” Else “S”

So then I made a cross table with posting date as column field.
Format it to show yyyy/MM (so without the day) and apply the filter on YTD_Filter field to show only “S” (show) then the cross table will me always show 13 months (ie. 2020/10 to 2019/10)

Ps: yes you could also direct filter on the Idicator. But in my case there was a second thing the filter needed to take into cocideration (let this out in my example)
:slight_smile: