How to get previous month data from current month values

[Former Member]

Hi Experts,

I have made one universe from BW Query in which Fiscal year period is entered in interval.

I have made a universe from that and want to develop webI reports on top of that.

In my webI reports, i have used one cross tab. In Rows section i have added Company Code and in Column section i have used Fiscal Year/Period and in Value section i have added Sales Value. I want this value of previous month.

Requirement:

Ex.

Feb'09 Mar'09 and so on...

Comp_code1 Sales of Jan'09 Sales of Feb'0f and so on....

I am getting this.

Ex.

Feb'09 Mar'09 and so on...

Comp_code1 Sales of Feb'09 Sales of Mar'09 and so on....

I hope i have clear my requirements.

Please help as soon as possible.

Thanks in Advance,

Rishit

[Former Member · Accepted solution]

Rishit,

Add a month to your fiscal year/period (+1) (Convert this to month format and add a month and then again convert it to your required format) in your formula bar and format it accordingly.

for ex:

=ToDate([Month]+1;"M") adding a month.

Regards,

Swati.

[Former Member · Accepted solution]

HI Swati,

Fiscal Year is not in measure form but it's in characteristic.

My fiscal year period values are 009.2009, 010.2009 and so on.

Now how can i add one month to my fiscal year period? and how i can i restrict my Sales Value on Next Month.

Thanks & Regards,

Rishit

[Former Member · Accepted solution]

Hi Rishit,

Follow the below steps to get the desired result.

Step1: Convert your fiscal year period from char to a date in your database or in your designer however its feasible.

to_date('substr('009.2009',2)','mm.yyyy')

you will get the result 01 sep 2009

Step2: Convert this format to 01/09/2009 by using date functions.

Step3: Create a Detail associated to the 'date' field (typically your fiscal period).

Step4: Create a cross tab Like : Rows section should have Company Code and in Column section should have 'date'(created detail) and in Value section should be Sales Value.

you should get the following result.

01/02/2009 01/03/2009 and so on...

Comp_code1 Sales of Feb'09 Sales of Mar'09 and so on....

Step5: Use the following formula in your Column (date) formula bar.

=(<date>-1)-DayNumberOfMonth(<date>-1)+1

You will get the following result:

01/01/2009 01/02/2009 and so on...

Comp_code1 Sales of Feb'09 Sales of Mar'09 and so on....

Format the cell according to your reruirement.

Let me know if you will get any break in the above steps.

Regards,

Swati.

[Former Member · Accepted solution]

Hi All,

you could use Previous function which is available in web i to get the previous value.

For example in a cross tab:

Dim1 as rows,dim2(monthname) as column and Measure.

Now if you want the previous month values in the cross tab

=previous([measure];Row)

this will give the prev month measure value.

Hope this will help you.

Cheers,

Ravichandra

[former_member142702]

Hi

you have to modify your BEx query for ths. Just add another variable that limits the number of fiscal periods returned so that also the values for the previous month are returned.

Regards,

Stratos

[Former Member]

Hi,

Can you tell me how can I limits the number of fiscal periods in Bex?

Regards,

Rishit

[IngoH]

hi Rishit,

do you have the data that is required available via the BW query already ?

ingo

[Former Member]

Hi Ingo,

I have current month data in BW Query. Is there any way by which I can get previous month data in WebI.

Regards,

Rishit