12 months rolling in webi report

[Former Member]

I need to create a webi report with data from (current month - 3) to (current month + 9).

I can access the report level. WEBI only.

So, may I know any idea on the formula to create this.

I tried use months between function but its not working.

Thanks.

[former_member207342]

1. Create a expression at report level to Current Month -3

2. Apply a filter on data block which contains data like Month > Current Month -3

3. Use RunningSum() function get desired output.

[Former Member]

Thanks for prompt reply.

Anyway, why I need to use RunningSum()?

Can I use betweenMonth() ?

I wanted to do betweenMonth(current month - 3, current month+9)

But the formula is not correct.

[former_member207342]

MonthsBetween() returns number of months between two dates.

you can use

RunningSum([Measure]) Where ([yourDateObject] >=RelativeDate()  AND [yourDateObject] <=RelativeDate() )

[Former Member]

Hi,

Can you clarify it a little bit? You're asking for help in creating the query or in calculating a Sum? Which are your objects?

Regards,

Rogerio

[Former Member]

I have a dimension column called (Months), I need to create a formula and apply it on that column.

because i need data from 12 month rolling i.e. from current month - 3 to current month +9

Therefore, I have to create a formula for this and apply it on that (Months) column.

the requirment is *Must contain data from 12 month rolling i.e. current month - 3 to current month + 9

for example;

     Now, current month is Oct.

     So, the report must contain data from [ Oct - past 3 months to Oct + upcoming next 9 months]

However, I tried to write a formula for that but seems not working.

Thanks a lot.

Regards,

Hikoe

[Former Member]

Okay,

But which set of data you already have in your report?

You are starting the report from sctratch? Or you already have a report with the desired data?

Regards,

Rogerio

[Former Member]

Yes, I am starting the report from scratch based on BEx query. I have one company number (hierarchy), months, receiving plant and some measures as internal sales. But the report must contain data of 12 months rolling which is as I mentioned above. Therefore, one formula has to be created and apply on the month column in webi.

[Former Member]

The problem with your approach is that you will have to filter your report for the 12 months only, lefting the other data aside. What I can suggest you is that you try bringing the data of last year through next year so you limit a little more your query results.

At report side  , create two variables [star date] and [end date].

Are you on wich version ? I read somewhere that 4.1 SP6 will make it possible to use RelativeDate with months, if so, try [start date] = RelativeDate([Current date];-3 months)

and [end date ] = RelativeDate([Current date]; 3 months) and filter your report by [date] Between

([start date];[end date]).

If you are on other versions ,you will have to deal with some sort of date manipulation, try something like that

[date as number] =ToNumber(FormatDate([date];"aaaaMMdd");"00000000")

[year as number] = Int([date as number]/1000000)

[month as number] = Int([date as number] - ([year as number] *1000000))/10000)

[start date] = If ([month as number] - 3 <= 0

Then   ""+([year as number] -1)+FormatNumber(12 - ([month as number]-3));"00")

Else    ""+([year as number] )+FormatNumber([month as number]-3;"00")

[end date] = If ([month as number] +9 >12

Then   ""+([year as number] +1)+FormatNumber( ([month as number]+9)-12);"00")

Else    ""+([year as number] )+FormatNumber([month as number]+3;"00")

As the first hypotesis, filter the report by [date] between ([start date];[end date])

Regards,

Rogerio

[Former Member]

Thanks a lot Rogerio.

I had tried it out.

I created two variables. [start date] = RelativeDate([current date];-92)
i.e. for last 3 months in days

                                        [end date] = RelativeDate([current date];+284)

i.e. for next 9 months in days

but when I do for the days (actually what I need is month) between and the start date and end date, it shows error.
(as shown in attached image)

In other way around, I tried another formula using IF-ELSE for last three months and upcoming nine months, it works. but I don't know how to get the months between them. between the start and end.

I search the pre-build formula , such as daysbetween, months between, all return (int). Hence, it return the number of days.

Thanks a lot for ur kind help. Its greatly helpful.

Best Regards,

Hikoe

[Former Member]

Hi I couldn't see the atachment

Regards,

Rogerio

[Former Member]

Here it is.

sorry, I slip my mind to attach it.

Thanks alot for helping me

[Former Member]

Hi,

I believe that [start date] isn't a date object.

Try converting it to date using ToDate([start date]; "aaMMdddd")

Regards,.

Do the same with [end date]

Rogerio

[Former Member]

MonthNumberOfYear(CurrentDate()) ---> returns this month (eg-Nov)

MonthNumberOfYear(CurrentDate()) - 3 --> return the last 3 month (eg-Aug)

I want to retrieve the data between that Aug to Nov.

RelativeDate(CurrentDate();Number)

is there anyway to use RelativeDate for Month to Month?

Todate not working.

Thanks and regard,

hikoe

[Former Member]

HI ,

Check this out,

It has complete information about Current month, Last month , YTD so on........

Surely it helps to make your requirement achieved...

Hope it helps you

[Former Member]

Thanks I will go through it