Conditional Sum based on Dimension

Lifesigns — Fri, 04 Jun 2021 10:27:32 GMT

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

topic Conditional Sum based on Dimension in App Development

Conditional Sum based on Dimension