rolling 12 month questions

[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?

[amitrathi239 · Accepted solution]

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

[Former Member · Accepted solution]

Thanks let me try 🙂

[Former Member · Accepted solution]

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 🙂

[amitrathi239 · Accepted solution]

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

[Former Member]

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

[amitrathi239]

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.

[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

[amitrathi239]

Then you have to create filter at Universe level.

[Former Member]

use want it at report level ...

[amitrathi239]

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

[Former Member]

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

[amitrathi239]

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

[Former Member]

awesome let me try

[Former Member]

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

[amitrathi239]

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

[Former Member]

its BI 4.1 sp4

[Former Member]

can we use any alternative

[amitrathi239]

check attached link.

https://archive.sap.com/discussions/thread/3484615