Report with subtotal fields - Copy subtotal to each relevant row

Hello - I need to add a column to the below report which would assign the subtotal field name (* ...) to each row of data it relates to. Which tool(s) should I use? Note there are other subtotals in the report with more than one row of data. Thank you!!

Common Use Cases

Accepted answers

IraWatt

I'm not entirely sure on your logic, can we identify what goes in the new column by the "*"? It looks like what you need though is a multi row formula tool (circled below) like this (attached workflow):

The multi row formula works top down so I sorted the records backwords to capture the logic from your screen shot. If you have any questions on how this workflow works make sure to ask

Multi row workflow.yxmd

IraWatt

Eeyyyy glad it worked @Becky_Chambers !

IF Left([Row-1:Cost Centers],1) ="*" THEN [Row-1:Cost Centers]
ELSEIF Left([Cost Centers],1) ="*" THEN NULL() 
ELSE [Row-1:New column] ENDIF

Maybe check out one of Alteryx's videos on multi row formula but essentially the tool goes row by row and applies logic for the new column. The top line says if the left most character in the cost centre above is "*" then take the value of that cost centre. If that is not true then second line checks if the left most character cost centre on the same row is "*" then put NULL. The final line just says take the value of the row above in the new column.

HTH (probably check the video if not )

Ira

All comments

IraWatt

Hey @Becky_Chambers,

Without knowing the full logic you want to apply its hard to say. The most common way to create a new column would be with a formula tool like this (attached example below):

However if you need to Parse with more complex logic the Regex tool maybe what you need

HTH,

Ira

Example.yxmd

Becky_Chambers

Thank you. I am summarizing the data by GL account and cost center. I would have previously formatted manually in excel by copying the cost center field into a new column like this:

Becky_Chambers

@IraWatt I added some more information below. Thank you!

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

Hollingsworth 335

LanisC 335