Rolling calculation for line graph

erjohnso — Tue, 24 Sep 2013 21:10:03 GMT

Green newbie here.

I've got sales dollar data by store, by quarter with # of customers. I have a simple line graph showing the average sales dollars per customer per quarter but I want to create a rolling 4 quarter average to plot as well.

Consider these simple data points,all stores rolled up:

Quarter Sales CustomerCount Average$perCust

1 $1,444 102 $14.16

2 $1,475 107 $13.79

3 $1,465 109 $13.44

4 $1,479 108 $13.69

Please see my attempts below. I am finding a discrepancy when I try to use above function to generate rolling totals and a rolling average. I've gotten these ideas from other threads in the forum...can someone tell me what I am doing wrong?

Rolling 4 quarter sales total = RSales: rangesum(above(sum(Sales),0,4))

Rolling 4 quarter customer total = RCust: rangesum(above(CustomerCount),0,4))

Rolling 4 quarter average sales per cust by code = RAvg:rangesum(above(sum(Sales),0,4))/rangesum(above(CustomerCount),0,4))

Rolling 4 Quarter average sales per cust by expression = RAvg2: RSales/RCust

Correct Rolling 4 Quarter Average = RAvg3: Manually calculated, what I am trying to return and graph

Quarter RSales RCust RAvg1 RAvg2 RAvg3

1 $1,444 102 $14.16 $13.08 $14.16

2 $2,919 209 $13.79 $12.96 $13.97

3 $4,384 318 $13.44 $12.84 $13.79

4 $5,863 426 $13.69 $12.87 $13.76

RAvg3 is what I want to return but as you can see, the 2 ways I have tried (RAvg1 and RAvg2) are off.

I really appreciate any advice here.

topic Rolling calculation for line graph in QlikView

Rolling calculation for line graph