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.
