1 Reply Latest reply on Jul 17, 2016 8:06 AM by Naveen Agarwal

    Column aggregation - Capturing the daily effort details for a large no of folks

    Ashutosh Priyadarshi

      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.

       

      Regards,

      Ashutosh