I have a business problem to solve with cumulative sums. I isolated the problem into below workflow:
When aggregating to cumulative sums for each project in the loop, the values are repeated after the last cumulation, if there are further months on this project. I understand that this is mathematically correct, but my problem is that I need to stop the cumulation after the last value and show it as “Null”/"?". See values highlighted in blue below. All those values should be question marks, I guess, so that when I export to excel the cell is empty.
In this wf max cumulative2.knwf (57.3 KB) the cumulative computation doesn’t stop. But after finishing the calculation a rule engine makes the repeated values missing.
gr, Hans
Your solution (@HansS) is great and solving the question in general. However, how should be handled the special case of a “zero” between two “non zero” values in Budget or Estimates columns for the same Project ? Is that a possible case in your data @Krau5i ? I guess the cummulative value should be kept in this case and not set to zero as in @HansS solution ?
this solution is mostly correct, but as @aworker points out, it does not account for “0” during a period. I have one example in the data set for project E. In this case, I would still expect to repeat 21.865 instead of a missing value. Any ideas?
Hi @Krau5i
By looking for the max value you are almost there. I made some adjustments in your wf. 1) Doing the calculation and the rule engine all together in the Group Loop (per Project). So only one loop instead of two. Because your max_values are only relevant within a Project. 2) Replaced the 3 math formula nodes with the multi-column math formula node. max cumulative 4.knwf (158.8 KB)
gr. Hans
These workflows are looking good, especially with regard to finding the max value to determine the end of the values.
One thing that got me wondering though (and it may not be applicable to this problem, but I can certainly imagine it being needed in similar problems) is the edge case where you have a negative value. Say at row 57 the “estimate” value showed -1 (some kind of “refund/credit” being expected maybe) , and then at row 59, the estimate were +1. Just using the “max” value unfortunately wouldn’t handle this (admittedly unusual) case.
So I’ve looked at this on the basis that you actually want to know for both estimate and budget columns, the last row containing a non-zero value, and then this can be used as the basis for setting the resulting cumulative values to empty/null. This is what I came up with. I couldn’t quickly see a specific function in a node that would give me the info I wanted directly, so of course there might be a simpler way of achieving this.
Hi @HansS
Thanks for the improvements. I really like the clean-ups you did, but it seems like the rule engines are not working anymore in the second workflow. I don’t understand why, but they don’t get rid of values when the max is reached. Do you have any ideas?
…
