Is it possible to create a calculated field that will create a schedule of tasks only on certain weekdays?
The context is let’s say Job #1 - Job #n and these tasks can only be done on Mondays - Thursdays. I would like to be able to create a calculated field that allows me to schedule what day I should expect to do each job. I am trying to accomplish this through a calculated field as I am using parameters that allow the user to visualize different work paces, and would like to allow that to reflect in the schedule.
I have a calculated field, Days Behind which calculates how many jobs we can expect to do in a day, based on parameters such as workers available. For example, all jobs with Days Behind =1 would be expected to be completed immediately, and Days Behind = 4 would mean that those jobs get completed 4 work days later.
Below is a mock table with my attempts so far. I have gotten it to work for stretches at a time but in this example at Job 56-57 in the mock data, the pattern breaks as Days Behind 12 should result in 16 calendar days later and a Date of Aug 1, but am getting Aug 3 instead.
Hello @shins, welcome to the QuickSight community!
Thank you for taking the time to set up a sample analysis in Arena, that will make it a lot easier for me to resolve the issue you are facing. My initial thought for something like this will be to use the truncDate calculation to set any of the date related fields we are using to a Day. If your calculation is working for jobs until the 56-57th job, then it might be something as simple as a rounding issue/timezone issue that is causing it to skip days.
When I went tested my idea in the analysis, I think I also realized that rather than expecting the output to be August 1, it should rather be August 5th. Which maybe this is because I am viewing this today but, 10 days remaining to complete after this coming weekend would be 3 more weeks to complete if Friday is considered part of the weekend. When I changed the calculation to use truncDate, it looks like the issue was resolved. Let me know if this helps!
Thanks for the example, I took a look at your mock-up as well, noticing that that some other dates also looked erroneous, for example Jul 20th was a Projected Date (Saturday) and Jul 22nd (Monday) wasn’t listed.
So I built an alternative here that seems to restrict the Projected Dates to Mon-Thurs only.
Thank you very much for taking the time to look at it! I believe your analysis may be correct about the rounding issue from little time discrepancies. Will definitely be adding truncDate
Thanks for taking a look at my issue! I think this is the outcome I was looking for. Could you explain the extra -1 when calculating the z Weeks field? I had issues where dates would be 1 day ahead or behind and had to add a modifier like this, but wasn’t exactly sure why.
The extra “-1” is to take into account that the “Day to weekend” field equals 0 the day before the weekend, so for my example to work I just made this modification so that the week numbers aligned. Alternatively, perhaps it would work to +1 to the “Day to weekend” calculated field.