I need some help with this problem, and I am hoping someone may be able to help, as I am not really able to figure out why cant we do it the most optimum way.
So here is the problem. I have a team of 50+ folks with more people joining in every few weeks. My requirement is to capture their effort for every single day of the year and then aggregate the values across months /quarters, etc.
Now if I want to keep it simple for the calculations, the conventional way to do it is to have one row for every day for every person, with a column containing the date and another one containing the effort. What that results in maintaining a workbook which contains (365* 50) ~ 18K rows for keeping an year's effort of people, making the sheet real heavy.
It also results in adding the 365 rows every time I have to add a single new resource.
What I ideally wanted to do was to have one column for every day, and then just adding a single row for every resource when they get added new. I can capture the efforts easily in this way, but then I just cannot aggregate in Tableau as every date becomes a dimension.
Has anyone got a quick solution for this ? Any optimizations in excel as well would be ok.
Have you considered setting up a database?
Also, I think you would need to store an ID for each person to keep track of their effort.