Caculating the sum of certain rows without transformation

Question

Dear all,

I currently struggle to find a efficient solution in my alteryx workflow for calculating the sum of certain rows. My goal is to automatically calculate the sum the figures in the blue box and display the result in the red box (see screenshot below).

Many thanks in advance for your support.

Regards

Dennis

RolandSchubert · Answer

Hi @DennisHolstein ,

you could use the account (or balance sheet position) number to create an aggregation rule. If I got it right, the first 3 digits could be used to identify the "top" position (Summe immaterielle Vermögensgegenstände = "133"), so you could use a Filter tool to select all rows having Left([Position], 3) = "133" and an "x" in field2 - the rows without "x" seem to be subtotals, e.g. 21 = SUM(22:26). The result of the Filter tool could be aggregated using a Summarize tool and the total added to the original data using a Join tool.

What do you think?

Best,

Roland

fmvizcaino · Answer

Hi @DennisHolstein ,

I've created a small example using running total tool where I insert the sum of rows in the row 1, there was the only way I could think of without any transformation whatsoever.

Let me know if this works for you.

Best,

Fernando Vizcaino

2021-01-05 Caculating the sum of certain rows without transformation.yxmd

aesanchez · Answer

It looks like the view shown is the desired output. Will assume that there is a table with a different structure that feeds it. A Filter tool or an IF statement in a Formula tool may help isolate the items that are to be added. A Formula tool can create the field corresponding to the Red box. An append tool can add the Red box field to the list of fields to publish. Some work will be needed to organize the output in the desired format.

Potentially, the tools in the Reporting family can help as well.

As @Qiu said, a sample file would be helpful.