Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
erjohnso
Creator
Creator

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.

0 Replies