topic Re: ADD MULTIPLE ROWS IN SAME COLUMN in Alteryx Designer Desktop Discussions

ADD MULTIPLE ROWS IN SAME COLUMN

britknip — Thu, 24 May 2018 05:18:38 GMT

I need to add three rows together that are currently in the same column and within multiple months.

I need a row called 'Variance' to add together

MFG COST

LESS ABSORBED - FGM

LESS ABSORBED - FGB

for each month. Sum for Actual, Budget and Last Year.

Thanks!

Re: ADD MULTIPLE ROWS IN SAME COLUMN

danrh — Fri, 11 May 2018 12:55:41 GMT

I'm not 100% positive on your requirements, but something like this?

This adds up the three rows by month, then adds a row at the bottom of each month called "Variance".

Hope it helps, reach out if this is off-base!

Re: ADD MULTIPLE ROWS IN SAME COLUMN

BenMoss — Fri, 11 May 2018 12:59:42 GMT

Here's how you can achieve this.

Start by transposing your data. Your Date and FSParent field will be your 'key fields' and your SUM fields will be your data fields.

Once you have performed your transformation, filter your data to only the rows needed. This will give you 9 lines for each date, 3 lines for each metric within that.

Then, use the summerize tool, to calculate your variance. This can be achieved by using the 'Group_By' on your date field and the 'Name' field (which represents the metric name) and summing the value field, I will rename this 'Variance'.

Next I will create a field which gives the name as it will appear in our overall list. Lets again give this as 'Variance'. I will name this column 'FSParent'.

My final step is to cross-tab this datasource so it is inline with the original data. This can be achieved by 'grouping by' our FSParent and Date field. Our column headers will be our 'Name' (the metric). And our value will be the 'Variance' sum column. Be careful, we have to rename our fields after the cross-tab if they contain special characters such as spaces.

Finally I will union this to our original stream.

Example attached.

Ben

Re: ADD MULTIPLE ROWS IN SAME COLUMN

BenMoss — Fri, 11 May 2018 13:00:23 GMT

@danrh's method is far more efficient! I always pivot when working with multiple columns but in this case it's not necessary.

Ben

Re: ADD MULTIPLE ROWS IN SAME COLUMN

bharti_dalal — Fri, 11 May 2018 13:04:18 GMT

hi @britknip,

i am attaching solved workflow. see if that helps.

Re: ADD MULTIPLE ROWS IN SAME COLUMN

britknip — Fri, 11 May 2018 14:21:56 GMT

I was able to get both @danrh and @BenMoss answers to work so thank you both!.

Also, these are great for summing but what if I wanted to subtract a field. For example:

Sales - Price Allowances = Net sales

Re: ADD MULTIPLE ROWS IN SAME COLUMN

danrh — Fri, 11 May 2018 14:40:01 GMT

For subtracting, I would add in a Multi-Field Formula tool to change the "Sum_" fields to negative for Price Allowance, then use the same approach to add them up.

I didn't find Sales records, so I made some assumptions: