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.
