12 Month Rolling Object , Need Help

[skkz]

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 the correct way to do it.

Please help !

Thanks
Techza

 

[Xander69]

Hi,

It looks OK to me, however it may be an idea to have the where clause as a separate filter object which you can then use with your amount object

Slàinte mhor

Jamie Alexander
Web Guru and All Round Good Guy

http://www.jamie-alexander.com