Here is a challenge I face frequently while using Knime.
I have multiple items in the Product Column, as seen in the attachment. I am trying to calculate the Rate of Change Percent for each Product based on the previous year. I am using the Lag Node to put the previous year next to the current year and then using a Math Node to create the rate of change. But where i have a problem is that where two Products meet in the list the rate of change would technically be incorrect.
Option 1: Using the Group Loop based on the Product Column.
You can use the Lag Node as you are doing but inside the Group Loop. Basically, you will end up doing the calculation per Product for each year
Option 2: Do an additional Lag on the Product column where you can then detect where the Product changes (if current Product != lagged one (previous one), then it means the product changed). When the product change, reset your lagged value to 0. This can be done via the Rule Engine node. Then continue with the calculation you were doing
Thank you! I do think both are viable solutions. I conceptually understand Loops and I have been trying to learn and use them, but I always struggle to get them to work correctly or at all.
Would you have a like formula I could use for the rule engine? I have never used this node and might be the most useful.
The Rule Engine node itself contains some documentation (as all nodes do) and some sample. It is pretty straight forward if you check the documentation there.
Basically: $column1$ = $column2$ => 0
means:
if column1 is equal to column2 then return the value 0
And you assign that value to the column that you want to set to 0 (either an existing column, or a new column)
This worked perfectly after messing with it for a while. I created both an offset column for the product and an offset column for the Dollar Sales. The Product column I created the rule to identify when the product switches and place a 0 in the offset Dollar Column otherwise the offset column is the previous row dollar.
