cancel
Showing results for 
Search instead for 
Did you mean: 

How to Display previous 12 months data in month wise (i.e Dec-2014-Jan-2015-Feb-2015)like that

Former Member
0 Kudos

How to Display previous 12 months data in month wise (i.e Dec-2014-Jan-2015-Feb-2015)like that

Please refer above

Accepted Solutions (1)

Accepted Solutions (1)

Henry_Banks
Product and Topic Expert
Product and Topic Expert
0 Kudos

what front end / client tool are you using?

Former Member
0 Kudos

SAP BO Web Intelligent.

Henry_Banks
Product and Topic Expert
Product and Topic Expert
0 Kudos

OK, I've moved you to the correct space.

do let us know version information too

amitrathi239
Active Contributor
0 Kudos

Hi,

If dates are available in the  report for one year.(At least one date for each month) then use like this.

Var==FormatDate([Calendar Date];"Mmm-yyyy")

If data is more then one year in the report then along with above Var create one more variable.

Var1==Rank([month];Top)

Apply Block filter on Var1 between 1 to 12.

Amit

Former Member
0 Kudos

We are using SAP BO4.0

Below is my issue:

User will select end date from that date(current date) to previous 12 months data in month wise

Example user selected 28-02-2015 then i want previous 12 months date from user selected date

FEB-2015--JAN2015--DEC-2014-NOV2015------MAR-2014

***Note: their is no start date. user will answer only end date.

Please help me any one.

amitrathi239
Active Contributor
0 Kudos

Hi,

In this case you need to add the Pre defined condition in the Universe to fetch the 12 months data based on the user input date.

If your back end is BW then you need to do changes in the BEx query filter to etch the 12 months data based on the user input date..

Other option is User always  enter from and to date in the prompt.

Better to do in the universe or if backend is BW then can do in the BEx query.

Amit

Former Member
0 Kudos

Hi

our back end is oracle

Please can u share pre define syntax with 12 month rolling back.

Former Member
0 Kudos

Hi,

Can u share syntax for that

our back end is oracle

amitrathi239
Active Contributor
0 Kudos

Hi,

Something like that create a predefined condition.

Table.Date between add_months(@Prompt('Enter Date','D',,mono,free),-12) and @Prompt('Enter Date','D',,mono,free)

Amit

Former Member
0 Kudos

Thanks you very much for u r valuable answer.

Once i got 12 months data how can i display in webi month wise

can u share sample syntax for one month.

amitrathi239
Active Contributor
0 Kudos

Hi,

Once you get the 12 months data and if you have Month/Year object available in the webi report then simply drag the Month/Year object in the report.

Other way drag the date filed in the webi query on which you have created universe condition and create one variable.

Var==FormatDate([Date];"Mmm-yyyy")


With this also you can display Month & year in the report.


Amit

Former Member
0 Kudos

Hi Amit,

But i dont want to see month name and year i want to see revenue in month wise

can u share syntax for that?

amitrathi239
Active Contributor
0 Kudos

Hi,

once you drag the month/Year variable and revenue object in the table,you will get the revenue by each month spilit.

Amit

Former Member
0 Kudos

Hi ,

Is their any alternative to achieve above scenario in webi report.with out touching universe.?

Thanks,

S Suryateja.

Former Member
0 Kudos

Hi Amit,

i have implemented @prompt as you mentioned in above now i got previews months data in report.

Above logic is not working for my scenario.

I want to display  revenue for each month in columns.

Like below.

Thanks,

S Suryateja

amitrathi239
Active Contributor
0 Kudos

Hi,

Did you getting the 12 months data in the report?

What are the objects dragged in the webi query.?

Type of values in the objects?

Share the screenshot of your Webi report base objects data view?

Amit

Former Member
0 Kudos

Hi,

I got 12 months data in the report

Objects used: 5 Dim objects and 1 measure i am using

if i select 15-08-15 as end date

in Below  Scrren shot

Period 1 column i want to see September-14 Data

Period 2 column i want  to see Octomber-14----

Period 3 column i want to see August-15.Like that

amitrathi239
Active Contributor
0 Kudos

Hi,

I am assuming you are getting one year date in the Period date.

Create V Period =Formatdate([Period Date];"Mmm-yyyy")

Create crosstab report and put 5 dimensions in the columns and V Period in the row and measure in below.

Amit

Former Member
0 Kudos

Hi,

In my case if their is not data for particular month i need to display that month also.

Thanks,

S Surya Teja.

amitrathi239
Active Contributor
0 Kudos

Hi,

In this case add one more query in the Webi query and only drag Period date and keep the same Prompt filter.

After that Merge both Period date from Query 1 & 2 and create variable on the Merged Period date only.See if it will work.

Or if you have some calendar date object in the universe from you will get all dates then use that particular object in the second query.

Amit

Former Member
0 Kudos

Hi

i tried Query1&2 with same period date it is not working. how ever i will try next option.

and period data date are not coming in order see below.

in my scenario


if i select 31-Dec-14 i want to see header like below.

Jan-14--Feb-14------------------Dec-14 like in order. it should be Dynamic.

Thanks,

S Surya Teja.

amitrathi239
Active Contributor
0 Kudos

Hi,

For sorting by month and year follow below steps.

Create variable Month=FormatDate([Period date];"Mmm")

Year=FormatDate([Period date];"yyyy")

Place these two variables above the Month-Year variable.

Right click on the Month->Sort->Advance->Custom Order->Values and select the Months order.

After this apply the Ascending order on the Year variable.

Check format is coming correct or not.

Right click on the Month variable and Hide it.Same for year Hide it.

Regards,

Amit

Former Member
0 Kudos

Hi,

Thank you very much.

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi

Create 2 user defined object at universe level using the following syntax

FIRST_DAY(TRUNC(ADD_MONTHS(SYSDATE,-12),'Month')) - return 1st day of previous 12 months

and

TRUNC(SYSDATE)  - Returns current date

Then at the query panel level drag the date object in to query filter make the condition is

Date between TRUNC(SYSDATE) <= and

FIRST_DAY(TRUNC(ADD_MONTHS(SYSDATE,-12),'Month')) >=


Try this you will get the previous 12 months data.


Thanks