Forecast graph (sum only workers relevant for date range)
Hi
How can I make a graph displaying numbers of workers relevant for a given period of time?
Details:
Sorry if this already has been answered somewhere. I have looked but not found anything to match my issue, although I believe this should be relatively common request.
Example data:
Workers: Load * Inline [ From , To , Worker , Cost 20.01.2022 , 20.08.2022 , Worker A , 1000 20.02.2022 , 20.09.2022 , Worker B , 1200 20.03.2022 , 20.10.2022 , Worker C , 1500 20.04.2022 , 20.11.2022 , Worker D , 2000 ];
To forecast I would like to handle these according to when they are relevant in the organization. This means relating only to those starting before this month (preferably before start of next period) and still work after start of relevant month.
I can express this with Excel formula: =SUMIFS(Cost;From;"<="&@RelevantDate;To;">="&@RelevantDate) ...or even better... =SUMIFS(Cost;From;"<="&OFFSET(@RelevantDate;1;0);To;">="&@RelevantDate) [Offset won't work with @RelevantDate, so to use it in Excel you'll have to replace with cell reference]
This is expected output
Good
(Better)
jan
0
1000
feb
1000
2200
mar
2200
3700
apr
3700
5700
may
5700
5700
jun
5700
5700
jul
5700
5700
aug
5700
5700
sep
4700
4700
oct
3500
3500
nov
2000
2000
dec
0
0
My primary goal is to be able to display the expected cost for all workers based on the database of expected start and end date. Preferably in a way that allows to drill into dimensions (department, roles etc). My secondary goal is to add this to the graphs for actual worklogs, so it's easy to visually compare forecast and actuals.
