12Months Rolling object , Need Help

[Former Member]

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

[Former Member · Accepted solution]

Hi,

The SQL syntax seems to be correct globally correct but it is preferable to manipulate dates rather months and you also forgot to aggregate your measure.

Try something like this:

Select 
SUM(TableName.Amount)
FROM TableName
Where
@Select(TableName\Date) 
BETWEEN (@prompt('Enter Date (Start):','D','TableName\Date',Mono,Free,Persistent,,User:0)  - 365)
AND
@prompt('Enter Date (Start):','D','TableName\Date',Mono,Free,Persistent,,User:1)

Didier

[Former Member · Accepted solution]

Hi Dider,

What about a leap year.

Regards,

Rakesh K

Edited by: BO_Guru on Jul 22, 2009 3:36 PM