1 Reply Latest reply: Dec 5, 2010 2:03 PM by Peter Rieper RSS

    Iterate range of dates breaking qty in appropriat week

    Maria.Delaney

      As ever, started on something I thought would be easy with QV and it is turning out to be a nightmare... In a ver simple example I have three fields [Start Date], [End Date] and [Qty]. I need break the date range down into constituent weeks and allocate the correct fraction of [Qty] to that week..

      As an example (dates in dd/mm/yy format for this old fashioned English girl)

      [Start Date], [End Date], [Qty]

      07/12/10, 21/12/10,33

      At this time I'd settle for something that creates three records (say with fields [WeekNo] and [WeekQty]) in an iterated loop as follows:

      w/c 06/12/10 with quantity 11

      w/c 13/12/10 with quantity 11

      w/c 20/12/10 with quantity 11

      But being such a perfectionist I'd really like it to aportion the right amount to each week by the number of working days in that week.

      w/c 06/12/10 has 4 working days (in the range specified) and so gets 12

      w/c 13/12/10 as 5 working days (in the range specified) and so gets 15

      w/c 20/12/10 has 2 working days (in the range specified) and so gets 6

      Hopefully that makes sense to some QV guru out there who can help!

      Many many thanks in advance,

      Maria