Sum of amount for rolling 6 months

[#1]

Hi Team,

I using this expression in test box for showing value in USD but actually i need to show rolling 6 months data based on current month dynamically..

i have year,month fields..

=num(Sum(ValueUSD)/1000000, '$#,##0.00')

****The rolling 6 months means for example we are currently in Apr i need to show the data for (Mar-14,Feb-14,Jan-14,Dec-13,Nov-13,Oct-13)****

Please help me in solving the problem..

Thanks,

Rahul

[its_anandrjs]

Refer this documents

Re: Rolling Past 12 Months

and this one

Calculating rolling n-period totals, averages or other aggregations

[MK_QSL]

If you have date field in your database, use below...

=num(Sum({<[YourDateField] = {'<=$(=MonthEnd(AddMonths(Today(),-1)))>=$(=MonthStart(AddMonths(Today(),-6)))'}>}ValueUSD)/1000000, '$#,##0.00')

Update : above solution tested and working fine in my application.

If it is not working, let me know the Format of YourDateField.

[#4]

I am able to do in chart but how to do it text box..

[its_anandrjs]

Write like this in text box

MonthYear = YourMonthyearField

=num(Sum( {$<MonthYear = {">$(=num(AddMonths(MonthYear,-6))) <=$(=Num(MonthYear))"}>}  ValueUSD)/1000000, '$#,##0.00')

[MK_QSL]

Try my solution in text box

[its_anandrjs]

Update

Write like below in text box

In MonthYear = YourMonthYearField and if not avilable month year then write in load script like

MonthName(YourDatefield) as MonthYear,

=Num(Sum( {$<MonthYear = {">$(=Num(AddMonths(MonthYear,-6))) <=$(=Num(MonthYear))"}>}  ValueUSD)/1000000,

'$#,##0.00')

[#8]

I have month and year two different fields like Apr , FY14 some thing like this....and i dont have date filed..

[its_anandrjs]

You can write this in Text box also

=Num(Sum( {$<MonthYear = {">$(=Num(AddMonths(MonthYear,-6))) <=$(=Num(MonthYear))"}>}  ValueUSD)/1000000,

'$#,##0.00')

Or

=Num(Sum( {$<MonthYear = {'>$(=Num(AddMonths(MonthYear,-6))) <=$(=Num(MonthYear))'}>}  ValueUSD)/1000000,

'$#,##0.00')

[its_anandrjs]

With the help of Month and Year field in load script make the month year field like

Try like this

Month&' '& Date('20'&Right(FinancialYearFieldHere,2),'YYYY')  as MonthYear

May be provide sample.