Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

Not applicable

Iterate range of dates breaking qty in appropriat week

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

1 Reply
prieper
Honored Contributor II

Iterate range of dates breaking qty in appropriat week

Hi,
would propose to calculate the daily Qty, assign it to each (work)day and then lateron do whatvever grouping you wish to do (daily, monthly etc).
Please check the attached example

HTH
Peter

Community Browser