Hi All,
Can't seem to get this one figured out...
I have a list of employees, the Item they booked time against and the forecasted time.
Now when there is a bank holiday I have to amend their forecast and remove the allocated bank holiday time, I thought that would be simple but can't get it right.
Here is an example:
Data...
| Name | Item | Forecast | Date |
| John Doe | Scone | 5 | 31/05/2021 |
| John Doe | Tea | 5 | 31/05/2021 |
| John Doe | Cake | 15 | 31/05/2021 |
| John Doe | Learning | 5 | 31/05/2021 |
| John Doe | Bank Holiday | 7 | 31/05/2021 |
| John Doe | Coffee | 5 | 31/05/2021 |
| John Doe | Scone | 10 | 07/06/2021 |
| John Doe | Tea | 10 | 07/06/2021 |
| John Doe | Cake | 15 | 07/06/2021 |
| | | 31/05/2021 | 07/06/2021 |
| Name | Item | Forecast | Forecast |
| John Doe | Scone | 5 | 10 |
| John Doe | Tea | 5 | 10 |
| John Doe | Cake | 15 | 15 |
| John Doe | Learning | 5 | 0 |
| John Doe | Bank Holiday | 7 | 0 |
| John Doe | Coffee | 5 | 0 |
| | Total | 42 | 35 |
The issue I have is that the forecast includes the bank holiday and the total forecast for John Doe should be 35 for that week not 42, so I was hoping I could create an expression that would take the largest one for 31/05/2021 which is Cake and subtract the bank holiday from there so Cake would be 8 and the total would be 35.
It's a large list with various employees and their items vary but the bank holiday always shows like that.
Appreciate any input!
Thanks,
Shane
