Skip to Content
0
Former Member
Jul 16, 2009 at 02:11 AM

12Months Rolling object , Need Help

28 Views

Hi,

I am trying to create a report in WEBI Where I have 3 columns

1)Date

2)Amount

3)12 months rolling amount

Date object I have formatted it in the universe to get the MON-YYYY, So that I can the data by months, And its working correctly.

In the report I am getting data like this.

Date Amount

Jan-2008 5000

Feb-2008 6000

Mar-2008 8700

Apr-2008 5500

I want another column for 12 month rolling amount which show the data from for last one year.

example : Jan-2008 for 12MonthRolling Amount column I should get the total of Jan-2007 to Dec-2007

Feb-2008 for 12MonthRolling Amount column I should get the total of Feb-2007 to Feb-2007

User will be having the prompt to select the Date Between

I have created the Measure for 12 month rolling amount to select the last 12 month total based upon the prompt.

In the select for this object I have

Select

TableName.Amount

Where

@Select(TableName\Date)

BETWEEN DATEADD(month,-11,@prompt('Enter Date (Start):','D','TableName\Date',Mono,Free,Persistent,,User:0) )

AND

@prompt('Enter Date (Start):','D','TableName\Date',Mono,Free,Persistent,,User:1)

I don't Know If I am doing it correct or not. Can anybody please suggest me or show me the correct way to do it.

Please help !

Thanks

Techza