Convert data to show each day of week and week summary

simonj · May 10, 2024, 4:22pm

Hi All,

I have a list of time entires, for march 2024 , date and hours. I am trying to convert this into a summary by week that shows the total hours per week and total per day , where each day is a column MTWTFSS.

I have got part of the way there with the attached transform, but not sure if my approach makes sense, or will hit performance issues , and also how to do a summary by week? I looked at Summary and Total transforms but didnt seem to be a way to sum all the hours for each day for each week based on my data.

any ideas?

DayOfWeek.transform (8.6 KB)

thanks!

Admin · May 10, 2024, 4:37pm

I would do it with WeekOfYear, DayOfYear and a Pivot. No need for Ifs.

DayOfWeek2.transform (5.2 KB)

This approach should scale to millions of rows, with no problem.

simonj · May 10, 2024, 5:14pm

thanks for this Andy, i also should have said that it still needs the week start and week end dates, is it still possible to add that detail to this using this approach?

Olaf · May 10, 2024, 6:41pm

DayOfWeek2 extended.transform (10.6 KB)

Start and end dates of the week are added, if they exist in input data.

Admin · May 10, 2024, 6:49pm

Here is another approach. Might get more complicated if you are spanning multiple years.

DayOfWeek3.transform (11.0 KB)

Anonymous · May 11, 2024, 1:52pm

Here is one.

I noted that in your data 31/03/2024 had total hours 39, I take it, that is total for the month? in my solution I removed this number and put arbitrary hours, so that the Sum of hours are correct. So if you try out my solution make sure you don’t have any total hours for the month or week in any of your dates.

Transform file.
DayOfWeekAndSummary.transform (10.6 KB)