topic Accumulation based on previous value in QlikView

Accumulation based on previous value

varunreddy — Thu, 24 Sep 2015 13:42:38 GMT

Hi Guys,

I have this as an input:

Date	Pace
1/1/2015	1
1/1/2015	2
1/1/2015	3
1/1/2015	5
1/2/2015	6
1/3/2015	1
1/3/2015	2
1/3/2015	3

I want this as an output:

1/1/2015 - (1+2+3+5)/4 = 11/4 = 2.75

1/2/2015 - (1+2+3+5+6)/5 = 16/5 = 3.2

1/3/2015 - (1+2+3+5+6+1+2+3)/8 = 22/8 = 2.75

Can any one please help me?

Thanks in advance

Regards,

Varun Reddy

Re: Accumulation based on previous value

Gysbert_Wassenaar — Thu, 24 Sep 2015 13:46:37 GMT

Try: rangesum(above(sum(Pace),0,rowno()))/rangesum(above(count(Pace),0,rowno()))

Re: Accumulation based on previous value

varunreddy — Thu, 24 Sep 2015 13:59:13 GMT

Hi Gysbert,

This is working.

Thanks a lot for your quick response.

Can you tell me how rowno() is used for in this expression?

Regards,

Varun

Re: Accumulation based on previous value

Gysbert_Wassenaar — Thu, 24 Sep 2015 14:08:56 GMT

The RowNo() function is used to calculate the current row number of the table in the evaluation of the expression. That value is used in the Above() function to specifify the interval over which the expression inside the above function is applied.

Re: Accumulation based on previous value

varunreddy — Thu, 24 Sep 2015 14:38:18 GMT

Thank You Gysbert

Cheers!