rolling for 5 days?

[Anonymous]

date,amount

1/1/2014,100

2/1/2014,200

4/1/2014,300

5/1/2014,400

6/1/2014,500

7/1/2014,

8/1/2014,700

9/1/2014,

10/1/2014,900

11/1/2014,1000

12/1/2014,

13/1/2014,1200

14/1/2014,1300

15/1/2014,1400

here i want calculate for rolling 5 days avg for sum(amount)  ?

how to calculate it?

[rubenmarin]

Hi, please check attachment, hope this helps

[Anonymous]

i am using personal edition pls give me expression here

[PrashantSangle]

Hi,

Try like

Avg({<Datefield={">=$(=Date(max(date)-5))<=$(=Date(max(date)))"}>}Sales)

IF dateField is not in date format then use below code in script to convert it into date

Date(Date#(date,'DD/MM/YYYY'),'DD/MM/YYYY')

Regards,

[SunilChauhan]

=Avg({<date={">=$(=Date(max(date)-5)))"}>}amount)

[Gysbert_Wassenaar]

See this document: Calculating rolling n-period totals, averages or other aggregations

[Anonymous]

it will give for only max date .

in my above data for every date it should calculate rooling avg

[SunilChauhan · Accepted solution]

Rangesum(above(sum(amount),0,5))/5

[Anonymous]

what is the representation of   '0'   here 

[SunilChauhan]

you could use 0 using if

using if (len(amount)=0,0,amount);

or null handling

NullAsValue amount;

Set NullValue = 0 ;

hope this helps