Stop calculating cumulative sums when last value is reached

Hi everyone,

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.

Does anyone have an idea how to accomplish this?

EDIT: Workflow attachedmax cumulative.knwf (22.6 KB)

Let me know if you have questions.

Thanks!
Tobias

2 Likes

Hi @Krau5i

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

4 Likes

Hi @Krau5i & @HansS ,

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 ?

Best,

4 Likes

Thank you @HansS and @aworker ,

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?

Thanks,
Tobias

I think I found it. This detour comparing with the max values for each project should do. max cumulative 3.knwf (38.3 KB)

1 Like

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

3 Likes

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.

max cumulative 5.knwf (40.7 KB)

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?

Thank you @takbb . This workflow works, although slightly more complex than what we had before.

1 Like

Everything should be made as simple as possible, but no simpler
- Albert Einstein…

Hi @Krau5i ,
Ok then, in tribute to Albert, (and because I can definitely see this being a useful generalised-component, I give you version 6…

max cumulative 6 - simplified.knwf (136.5 KB)

The component is available here… (Feedback on improvements, or issues found welcome!)

I think it works, although I’ve spent only a limited time testing it!

1 Like

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