Summing up data daily, weekly, monthly by each user

[takbb]

Hi @Karlygash , I’ve made quite a lot of modifications, and I kind of lost track of whether this resolves most or all of your questions. I’m also not quite sure where you were going with the additional 5 empty rows , and maybe what I have now removes the need for them anyway, so if you still need to make that bit work, please fire back with another question…

I figured that the main problem you had was how to put new “total” lines in in the correct sequence compared with all the other lines, so what I did was insert an additional column called “LineSortNumber”. This gets added to the sorter, so you still sort by Employee and the date, but in addition it includes the LineSortNumber in the sorting order.

This Line sort number has the following arbitrary values:
10=the lowest level “time period in a day” value
50=Daily Total value
100=Weekly Total Value
200=Monthly Total Value

So if these are added to the sort order, the lines for a given day appear in the correct relative place, and you don’t have to try to include lots of other columns in the hope that the sorting works out.

There is a new column “Transaction Apply Date - Sortable”, which holds the original Transaction Apply Date so that it can always be used for sorting in correct date order, even when you have placed a label such as “WEEKLY”, “MONTHLY”, or “dd-MMMM-yyyy Total” in the Transaction Apply Date column.

And I changed the way the grouped Daily total values get applied to the Daily Total rows. These are now applied using a JOIN whereas before they were being concatenated and so there was effectively duplication of the daily total row where some columns on each were populated. I hope that now makes it more correct.

I tried to put a red box around every node I touched, as it was becoming quite extensive, so sorry if you have moved on with this in the meantime as it might make it a challenge to “stitch back together”.

I also included some column filtering on the rows to try to remove some of the values that made no sense for that row (e.g. remove weekly or daily value from a Monthly row, and remove monthly value from a Weekly row, etc)

I hope this makes sense. I may have missed stuff so please ask again if there is anything else, or it doesn’t make sense.

Good luck… enjoy!

contingent worker_beta-2.knwf (953.3 KB)

[Karlygash]

@takbb , good day!
Sorry, I was not able to look at your workflow last two days (got vaccinated …)
Just have reviewed your solution
Firstly, thank you for clear explanation
Secondly, I really like your solution, yes, it is tricky, but it solves everything
Thirdly, I really appreciate your help and support
Thank you for your help! Totally agree with @aworker
You are master

[aworker]

Hi @Karlygash

Indeed @takbb is the best . Kudos to @takbb for this achievement !

Best wishes to you both & enjoy your weekend !

Ael

[takbb]

Thank you both @Karlygash and @aworker. Always a pleasure working with both of you. And @aworker you are being too modest. I’ve seen some of the solutions you have provided on many aspects of KNIME that I simply have no idea about so I am learning a lot from you too. It is our different experiences along with many of the other regular contributors here that make us combine to be a great “team” !

Glad the solution worked for you @Karlygash and that it made sense. The trick of adding an extra value to work as a “line sequence number” is one that can often come in handy when writing reports containing subtotals.

Glad to hear that you’ve had the vaccination. Hope you are feeling OK now. When I had my first one, I felt ok but I was really tired for a week.

Have a great weekend too both of you!

[Karlygash]

thanks, @takbb , @aworker have a very productive week