How to create an accounting week

Hello. I have a table with number of rentals by day from 01/04/2023 – 10/03/2023. I want to group the rows into weeks and sum the totals for each week. Our accounting weeks run from Wednesday to Tuesday. I can do this in Excel but I want to learn this in KNIME. Thank you for your help!

Start with the Extract Date&Time node. I like to pull the day of the week in both name and number. For groupings like this I tend to use the number of the day for easier expression writing. If you want to upload some dummy data that has the same table structure, then we can help with the workflow a bit if needed.

Come to think of it, You can probably pull it off using the Week # and Year easier. Use the lag column on Week # to drop it down until it lines up with Wednesday as the start day. Then you could concatenate the lagged Week# with the extracted year to have a group code for your calcs.


That did the trick! I lagged 3 days on Week # then used Missing Value node to get the first three days to show as week 1. Thank you so much for the help! I’m forcing myself to not use power query in Excel and instead learn ETL in KNIME.

1 Like

Good call! It will pay big benefits in the end to get proficient in a multi-tool like KNIME. There isn’t much that you can’t be done in KNIME between all of the integrations, nodes and components.

Please marks the post as a solution to help future searchers pinpoint a quick solve. Thanks!

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.