Skip to Content
0

rolling 12 month questions

Nov 17, 2017 at 11:40 AM

82

avatar image
Former Member

Hi Guru

Is there a way to create a dimension or other date range filter allowing me to return results based on a rolling 12 month period?

For example, I’m returning the prior 12 months and current month-to-date usage, e.g. sep 2016-sep 2017. However, I’d like to change this so that I return prior 11 months (eg dec2016 to Oct 2017) and current month-to-date?

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
AMIT KUMAR
Nov 21, 2017 at 09:32 AM
0

follow these steps if you are on Bi 4.1 SP04.

create variable

V 12 month back date=ToDate(FormatDate(CurrentDate();"dd/MM")+"/"+FormatNumber((Year(CurrentDate())-1);"###");"dd/MM/yyyy")

create another variable.

Show/Hide=if([your date object] between ([V 12 month back date];currentdate())) then "Show" else "Hide"

Apply filter on table where Show/Hide variable where equal to Show to restrict data for 12 months

Show 3 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Thanks let me try :)

0
Former Member

seems like between also dosent work for sp4 it saying invalid data type for V 12 month back variable .

Thanks a lot for your time :)

0

it seems your date object data type is not date.it may be string.

0
AMIT KUMAR
Nov 17, 2017 at 08:21 PM
0
Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Nov 20, 2017 at 07:04 AM
0

Thanks @amit.kumar71

is it possible to achieve rolling 12 months data based on user response in webi .

as per my understanding we cannot create User response at webi level .

please correct me if i am wrong .

Thanks,

Anuraag

Show 12 Share
10 |10000 characters needed characters left characters exceeded

how many months data are you pulling in webi report?

is report prompt based on the date range?

if you are pulling more then 12 months data in the report then yes with variables you can display 12 rolling months data in report.

0
Former Member

Thanks for the reply

we need to pull continuous roll of the prior 11 months and current mtd. So if today is Nov 15 ,2017 and I run a report, I want to return activity from Dec 1, 2016

I thought of using =RelativeDate(CurrentDate();-365) but it may be a issue with leap year .

If user selects 20 nov 2017 we should get a data from 5 dec 2016 to 20th nov 2017 and current MTD

0

Then you have to create filter at Universe level.

0
Former Member

use want it at report level ...

0

how many months data are you getting in report level? what is the report prompt?

0
Former Member

right now we are getting monthly data and report prompt is current date or todays date

0

let say your report is running on current date.

create variable V 12 month back date=RelativeDate(CurrentDate();-12;MonthPeriod)

create another variable.

Show/Hide=if([your date object] between ([V 12 month back date];currentdate())) then "Show" else "Hide"

Apply filter on table where Show/Hide variable where equal to Show to restrict data for 12 months

0
Former Member

awesome let me try

0
Former Member

getting error missing operator or parenthesis in relative date postion IES 10067

0

what is your BO version? Monthperiod function is available from BI 4.1 SP06.

0
Former Member

its BI 4.1 sp4

0
Former Member

can we use any alternative

0