Based on selection - calculate Rolling weeks

[#1]

Hi All

Based on selection, i wanted to find the last three weeks sum + current week sum.

Data is not present for all the weeks.

So data will be

week 

1      

5

9

17

19

So if user select week 19. I need sum of 19 + 17+9+5.

Can some one help me out?

[swuehl]

Would it be feasible to create a sequential record number for each week?

e.g.

week      weekID

1             1

5             2

9             3

17           4

19           5

You could probably add this ID in your load script to your table (for example using recno() function). If your actual data is more granular (e.g. based on days), you could create a linkage table with the unique weeks and then create weekIDs.

If you got them, your sum could look like this:

=sum({<weekID={">$(=max(weekID)-4)<=$(=max(weekID))"}, week= >} VALUE)

This is a set expression which selects (only for this expression, not globally in your application) the weekIDs which are between max(weekID) and max(weekID)-4. max(weekID) should give you the ID of user selected week.

Regards,

Stefan