Working backwards from a date using Changing Quantity

[LiamB]

Hi All,

Quite a complicated one (for me anyway!).

I have a scenario where  there is an ability to perform an action (dig a hole) 50 times a day. I know that I need to do dig 115 holes, taking 3 days. I have a date where these holes need to be dug by, and want to record when I need to start digging holes. I can do this, however what I also would like to record is the Qty on each date so:

Date           Required date     Days to Dig     Holes Needed     Quantity     

15/12/17      15/12/17              3                      115                     115   

14/12/17      15/12/17              3                      115                     115

13/12/17      15/12/17              3                      115                     65

12/12/17      15/12/17              3                      115                     15

Obviously there will be numerous orders, I have set a record Id for each order and generated extra rows for each date, and made a 'Days to Dig' field. I'm just not sure how to work back and dynamically change the quantity. In the end result I'll be putting this into a chat showing Date and Quantity, with dig times.

Hope it makes sense and that you can help,

Liam

[Claje]

Hi,

I'll post again with some ideas for a solution shortly, but I wanted to ask one question:

It looks like right now you are taking your remainder of holes and digging them first (eg day 1 we dig the fewest holes).  Is this the way you want holes to be dug, or would it be ok to dig 50/50/15 instead on days 1/2/3?

[LiamB]

Preferably to be dug with the remainder on the last day (50,50,15). Sorry for the confusion.

[Claje]

So the best way I found to do this is to use the Multi-Row Formula tool.  You'll be best off using a Sort tool and sorting by Date - Ascending beforehand, so that we can count up rather than trying to count down, in my opinion.

If you create a new field named QTY in the Multi-Row Formula tool, the following formula should work:

[Row-1:QTY]+BOUND([Holes Needed] - [Row-1:QTY],0,50)

Basically, this will take the last row's quantity and add to it the number of holes needed minus the last row's quantity, up to a maximum of 50 per row.  The 0 in the BOUND() formula is there so that we never create a negative number.

hope this helps!  Happy to advise more if you have further questions

[LiamB]

Hi,

Thanks for the quick reply! Unfortunately it's not quite working how I need it to.

Basically, in my Data set I have a Start Date and an End date. I have generated rows for every day in between those dates, +/-5days at the beginning and end (extra days for digging the holes and filling them in after the required dates). I chose 5 days either side because I know I will never need to use more than 250 holes per job, but it some jobs will be less, so not all those dates should have a quantity.

If my start date is 15/12/17, end date 25/12/17, I have rows for that job ID going from 10/12/17 - 30/12/17.

If I need 115 holes, that's 3 days.

So Quantity on 10/12/17 and 11/12/17 need to be 0 (as work hasn't started yet) and 12/7/17 is 50, 13/7/17 is 100, 14/7/17 is 115 ->the required holes always needs to be the day before. I need it to do the same at the end date, counting from 26/12/17 at 65 holes, 27/12/17 at 15 holes and 28/12/17 onwards 0.

Then the calculation will need to reset the Quantity for each job to 0 and start again for the new quantity. At the moment it is accumulating the quantity down the table.

This is all about planning for the future, these dates would ideally all be in the future, it is about seeing where we will have work and need to organise our 'diggers'.

Hope this makes sense and is doable, thanks again for your help!

[danrh · Accepted solution]

Something like this?

[LiamB]

That is bloody fantastic!

I had to tweak it and add an extra if statement for when the order is in progress, as the amount of holes required might change at that point, but once again, this is amazing.

Cheers for both of your help, appreciate it a lot!

Liam

[LiamB]

Hi,

Like I said, this works great, however I have another stipulation that I need to add in.

The data gets updated daily, and there is a field (PreDugHoles) that shows the amount of holes that are already there - this only has quantities when Today() is before the Start date, and a hole has been dug. 

So basically what I need is , if Today() is after Date(that was added in generate rows) and the Date is within that range then I want it to show PreDugHoles -100 until 0 for the dates before, and +100 until HolesRequired after. 

I can do the if statement for the dates, I just don't know how to do the multi row formula to work out the holes going back in dates.

Any help would be great,

Cheers,

Liam