0 Replies Latest reply: Sep 24, 2013 5:10 PM by Eric Johnson

# Rolling calculation for line graph

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.