How to get the cumulative quantity and cumulative value when there are multiple products

[vinothkumar]

what tools would help to get the cumulative quantity and cumulative value when there are multiple products -see the excel attached

will multi-row formula help?

[grossal · Accepted solution]

Hi @vinothkumar,

you are right, you can achieve this with two Multi-Row-Formulas! Take a look:

Settings:

The two important options are:

1) Group by Product Name

2) Values for Rows that don't Exist to Null so that my "isNull"-Check to identify the first value works

Workflow attached. Let me know what you think

Best

Alex

[AbhilashR]

Hi @vinothkumar, you could also use Running Total tool to get cumulative numbers.

[grossal]

Is the Running Total able to use the starting value from a different column?

[AbhilashR]

I dug deeper into the data and now realize Mutli-Row Formula tool is a better fit in this case. Please ignore my post.

[vinothkumar]

Sorry, I added the wrong columns in previous file,.see the correct file.. how to get the values now?

[grossal · Accepted solution]

Hi @vinothkumar,

I have adapted the workflow. The only thing that needed to be changed was the starting value in the first Multi-Row-Formula.

IF isNull([Row-1:Cumulative Quantity-By Product])
THEN [Opening Qty] + [Transaction Qty]
ELSE [Row-1:Cumulative Quantity-By Product] +[Transaction Qty]
ENDIF

In the "Then"-Part it was called [Opening Qty Value] before. I replace it with the correct column and that's it.

Workflow attached.

Best

Alex

[vinothkumar]

How to remove the lines repeating in the subsequent rows using multi formula tool? I want the value to appear only for 1st of each month of the product and not for restOf the dates

[grossal]

You can achieve this using the Sample-Tool:

The Sample Tool allows you to group by a column, this effectively gives you the first of every category.

Output:

Workflow sample attached.

Best

Alex