1 Reply Latest reply: Jul 29, 2011 5:41 AM by Stefan Wühl RSS

    Based on selection - calculate Rolling weeks

      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?

        • Based on selection - calculate Rolling weeks
          Stefan Wühl

          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