Display Prior Month Revenue along with Current Month Rev

[Former Member]

Hi All,

My requirement is need to display the revenue along with PriorMonth revenue when user selects any business month date.

My  data like as below

Country      Business Date     Revenue
USA           31/01/2010              5000
USA           28/02/2010              6000
USA           28/02/2010              4000
USA           31/03/2010              4000
UK             31/01/2010              2000
UK             28/02/2010              1000
UK             31/03/2010              3000
INDIA         31/01/2010              3000
INDIA         28/02/2010              8000
INDIA         31/03/2010              8000
INDIA         31/03/2010              9000

Note: Montly Data (Last day of the month of the year  is the Business Date).

I need a report like as below

Date Prompt  as User choice through calaendor (User should select lase day of the month through calendor)

If user selects Date as : 28/02/2010 . The data should disply as below

Results:

Country       Business Date   CM Revenue       PM Revenue       Status

USA             28/02/2010         10000             5000               Increased
UK               28/02/2010         1000               2000               Decreased
INDIA           28/02/2010         8000               8000               No change

Please provide a solution how to achieve this.

Thank you very much .

[former_member184594 · Accepted solution]

What is your universe based on? Is it realtional database or SAP BW?

If it is BW, then you should do this in Bex query designer. If it is not, then you can do with @prompt function in universe designer.

If your universe is based on MS SQL then use this sql.

sum(case when Table.BusinessDate = DATEADD(month, -1 , @Prompt('Enter Business Date:','D',,mono,free,Persistent)) then Table.Revenue else 0 end)

[ravi_chandra3]

Hi

Are u creating the report on the top of the bex query. and is this bex query created  on the top of the infocube . If so , then the values are merged at the infoprovider level , i mean the data at the infoprovider level should be like this..

Country	Business Date	Revenue
USA	31/01/2010	5000
USA	28/02/2010	10000
USA	31/03/2010	4000
UK	31/01/2010	2000
UK	28/02/2010	1000
UK	31/03/2010	3000
INDIA	31/01/2010	3000
INDIA	28/02/2010	8000
INDIA	31/03/2010	17000

Here the data will look like this....

Hope u got it,

Regards,

RaviChandra

[Former Member]

Hello Babu,

I am not very sure if the following solution will work for you. But please do give a try.

Create a variable say "Previous Month Business Date" with the definition:

=RelativeDate([Business Date];-DayNumberOfMonth(LastDayOfMonth([Business Date])))

Create variable "PM Revenue"

= [Revenue] where([Business Date]=[Previous Month Business Date])

I guess once you display the previous month revenue you can write a formula to display the status.

Regards,

Madhumitha

[Former Member]

Hello Babu,

Also, If the user selects date from the calender, i guess the time stamp also gets displayed. Make sure the time stamp is eliminated in the Business Date column