Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

erjohnso
Contributor

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.

Tags (2)
Community Browser