Hi-
I’m somewhat new to Knime and have hit a wall. I’m trying to use Knime to do what I can do in Excel. I want to calculate SUMIFS based on multiple criteria.
Criteria to use:
Customer #
Product Code
I want to calculate trailing 30 day stockouts based on Customer/Product/Date.
I’ve looked at multiple examples of group by, column aggregator or loops and I can’t seem to figure this out.
I’ve attached an example of the data as well as the desired outcome.
Question on SUMIF came up a couple of times in the forum. @ipazin created an example for this and shared it in this thread: Sumifs Excel - #14 by ipazin
Thank You @Marten_Pfannenschmidt for the example workflow. Where I am getting stuck is the Sum IFS to sum between dates. I’ve attached a sample workflow that I was using to achieve this, however its resulting in an empty table.
OR
Does this need to be setup with a rule based filter within a loop?
You should loop over your criteria (use Group Loop Start node to define grouping columns) and in each iteration perform your calculation. Check workflow example where I got your desired outcome. Though not sure I got your logic with dates properly. Sumifs Example_ipazin.knwf (40.6 KB)
Hi @ipazin-
Thank you so much for the help! Your workflow is a big help! But I am still not able to get the desired outcome based on the date logic.
Date Logic: Trying to sum the Stock Out column based on Prior 30 days from date of “TicketDate” by Customer/Product.
I re-did my work flow based on your logic, but still not able to calc the prior 30 days. For example, see excel file.
Example:
For Customer 4680 on Ticket Date 4/7/2021, the trailing 30 day range is until 3/8/2021. So therefore the Prior 30 Day Stock out should be 1. Current Knime workflow is still calc 4.
then seems you need to go row by row, filter one month data based on it and sum it. Check out workflow attached: Sumifs Example_rework_ipazin.knwf (83.2 KB)
