Display three prior months starting from current month

[Former Member]

Hello All,

I came across the requiremnt:

The report looks like:

	2014-Jan	2014-Feb	2014-Mar	2014-Apr	2014-May	2014-June	2014-July	2014-Aug	2014-Sep	2014-oct	2014-Nov	2014-Dec
A	122	233	233	233	233	233	233	233	233	233	233	233
B	22	33	33	33	33	33	33	33	33	33	33	33
C	33	33	33	33	33	33	33	33	33	33	33	33
D	333	3333	3333	3333	3333	3333	3333	3333	3333	3333	3333	3333

The requirement is to display the prior 3 months ,current month, next 3 months.

Assume that current month is SEPTEMBER, the report should look like:

	2014-June	2014-July	2014-Aug	2014-Sep	2014-oct	2014-Nov	2014-Dec
A	233	233	233	233	233	233	233
B	33	33	33	33	33	33	33
C	33	33	33	33	33	33	33
D	3333	3333	3333	3333	3333	3333	3333

Share your thoughts.

Thanks

[Former Member · Accepted solution]

Hello All,

I got this resolved using the below formula to get either prior 3 months or the next 3 months from current date:

3rd month from current date:

=LastDayOfMonth(RelativeDate(LastDayOfMonth(RelativeDate(LastDayOfMonth(RelativeDate(LastDayOfMonth(CurrentDate()); 1));1));1))

Prior 3rd month from current date:

=ToDate(FormatDate(RelativeDate(ToDate(FormatDate(RelativeDate(ToDate(FormatDate(RelativeDate(ToDate(FormatDate(CurrentDate();"MM/yyyy");"MM/yyyy");-1);"MM/yyyy");"MM/yyyy");-1);"MM/yyyy");"MM/yyyy");-1);"MM/yyyy");"MM/yyyy")

and used these objects to restrict my date field(date object between "Prior 3rd month from current date:" and

"Prior 3rd month from current date:"

Thanks,

Vijay

[nikhil_joy2]

Hi Vijaya,

I assume that you are connected through BICS connectivity. You can create a key figure structure in bex query which will contain 7 restricted key figures  restricted by the month variable with offset.

for eg:- if ZMONTH is the variable selected by the user, your restricted key figures will be restricted based on the selected month. Sales where month=ZMONTH, ZMONTH-1,ZMONTH-2,ZMONT-3,ZMONTH+1,ZMONTH+2,ZMONTH+3. so total 7 restricted keyfigures will be there and you can have text variable to dynamically display the month and year name for each one.

If there is no selection, and always refer to current month, you can have an exit variable which always takes current month instead of ZMONTH.

Regards,

Nikhil Joy