topic Re: rolling for 5 days? in QlikView

rolling for 5 days?

Anonymous — Fri, 26 Dec 2014 13:19:41 GMT

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?

Re: rolling for 5 days?

rubenmarin — Fri, 26 Dec 2014 13:27:10 GMT

Hi, please check attachment, hope this helps

Re: rolling for 5 days?

Anonymous — Fri, 26 Dec 2014 13:34:19 GMT

i am using personal edition pls give me expression here

Re: rolling for 5 days?

PrashantSangle — Fri, 26 Dec 2014 13:38:21 GMT

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,

Re: rolling for 5 days?

SunilChauhan — Fri, 26 Dec 2014 13:48:58 GMT

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

Re: rolling for 5 days?

Gysbert_Wassenaar — Fri, 26 Dec 2014 14:01:28 GMT

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

Re: rolling for 5 days?

Anonymous — Fri, 26 Dec 2014 14:35:14 GMT

it will give for only max date .

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

Re: rolling for 5 days?

SunilChauhan — Fri, 26 Dec 2014 14:56:56 GMT

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

Re: rolling for 5 days?

Anonymous — Fri, 26 Dec 2014 15:14:46 GMT

what is the representation of '0' here

Re: rolling for 5 days?

SunilChauhan — Fri, 26 Dec 2014 15:20:20 GMT

you could use 0 using if

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

or null handling

NullAsValue amount;

Set NullValue = 0 ;

hope this helps

Re: rolling for 5 days?

Anonymous — Fri, 26 Dec 2014 15:31:56 GMT

if i take out the 0 what will happen is it effect on result

Re: rolling for 5 days?

SunilChauhan — Fri, 26 Dec 2014 15:34:19 GMT

no,not at all