Hi all,
I’m currently looking for a way to achieve a cumulative sum per item.
Here’s an example of what I’m looking for (last column)
Would any of you know how I could achieve this? The aim here is to obtain the exact number of staff on a specific date.
If I am understanding correctly, you want to know how many people on each date. So based on what you showed, there is a second row somewhere that also has 14.03.23 as their date, correct? Which is why Yan’s line shows a 2 as the cumulative sum.
If this is true, and I am assuming your dates can only be today or in the past?
Here is a potential solution:
You need two boards. Let’s call them Date Board and Log Board.
The Date Board will have an item created each day automatically and you will set a date column as today. I would suggest you trigger this very early in the morning to make sure all logs for that day happen after the item is created. You can always create the item the day before and have monday set as today then push 1 day.
You will also need a connected boards column to the Date Board, I would do a two way connection.
On the log board, I would suggest you only let people create items via a form. They should fill out their name as the item name, type of movement as a status column or dropdown, depending if it is a single select or multi-select type question, and a date question defaulting to today (assuming you are encouraging people to log their movement right away).
Set up an automation from the automation center that when the item is created to match and connect the item to the Date Board where the dates match.
Then on the Date Board set up a mirror column that displays the sum result of the formula or numbers column depending on what you chose above.
This then should show you on the Date Board your cumulative sum you want.
Alternatively you can always use a graph/chart where the x-axis is the date and the y-axis is the count of items. But if you want to see it in a table format I don’t know another way to do this.
Let me know if you need further support @jbraun
Dear Chris,
First of all, thank you for taking the time to reply. This table is in fact intended for our Human Resources department. The aim is to keep track of all staff movements, departures (-) as well as arrivals (+). That’s why we can find lines in the past, of course, but also in the future! One of the main purposes of this table is to be able to say “how many people” are working, will be working or were working in our company on a given date.
However, I’ll see if I can adapt your solution to our problem. I’m not (yet) a Monday specialist and English is not my mother tongue, so I’ll need a bit of time to do this properly.
In any case, I’ll keep you informed.
Sincerely
Ok. So what you are trying to do is track on any given date how many active staff there were in the company based on changes in an HR board.
So when someone starts add 1 to the staff count as of that start date.
When someone leaves remove 1 from the staff count as of that end date.
I think you might be able to do this with a chart. But it might be hard to explain it here. I’ll try.
On the board have a date column capturing the activity date (start/end date) so when someone starts set the activity date as today and when someone’s end date arrives set the activity date as today. Also have a status column for the ‘movement’. Then have a formula column as mentioned or set a numbers column based on the movement. You can have an automation that says when start date arrives set movement to start and set number to 1. And have a second automation that says when end date arrives set movement to ended and set number to -1.
Then on the chart use the activity date as the x-axis and the numbers column as the y axis and use the cumulative sum option. I think that should result in what you are looking for (I have not tested this theory).
