Calculating acumulated values by date and operating on them

[Emilio]

Hi there

Based on a product suscription and unsuscription table by date, i need to calculate the accumulated number by date and after a while i have run out of options which do not requiere programing.

My input table is as follows:
Product_id, date, number of contracts,

where i have two rows per day, one with positive values (suscriptions) and other with negative values (unsuscriptions).

From that i need to end up having a table with
Product_id, date, suscriptions, unsuscriptions, accumulated_today

where accumulated today is calculated based on accumulated_yesterday (value of the previous row) + suscriptions - unsuscriptions.

I do not have any issues going from my initial table to a table with
product, date, suscriptions, unsuscriptions, but i do not know how to generate for each row, the accumulated.

Not all the products have suscriptions or unsuscriptions all days and obviously i can not mix product data (so once i have the calculations for product1, i need to start product2 accumulated values from 0 on the first day).

Any ideas?

[ipazin]

Hi there @Emilio,

here is one. After you got your desired format (product, date, suscriptions, unsuscriptions) you can use Group Loop Start node to iterate over product_id. In loop body use Moving Aggregation twice. One to calculate subscriptions cumulative sum and one to calculate unsubscriptions cumulative sum. To get accumulated for each day just sum two cumulative sums with math Formula.

Br,
Ivan