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

    Rolling calculation for line graph

    eric Johnson

      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.