topic Re: Round Multiple columns in the same Data Set in Alteryx Designer Desktop Discussions

Round Multiple columns in the same Data Set

Karl_Spratt — Wed, 26 Jan 2022 18:02:59 GMT

Hi Community,

Looking for some help please, I have the booking for 2021, I will be adding to this so I want my workflow to use last 12 Mths Data I want to use these to create a future FC for next 12 mths, with a growth factor 1.2 rounded (Which I think I've managed so far 😀

Now I'd like to see the average value in the 12 month future forecast, the Formula should exclude any months in the 12 Months future Data for the item with zero "Forecast" so as not to deflate the average), then using this average I'd like to replace any value in the future forecast which exceeds the average with the average.

So in the example below is the rounded average = 7, I'd like to replace the 3 values above 7 with 7.

Item Number	New_1	New_2	New_3	New_4	New_5	New_6	New_7	New_8	New_9	New_10	New_11	New_12	Average
47703046001	23	2	1	6	6	8	5	5	1	13	6	2	7

Can some please explain to me how to do this.

TIA,

Karl

Re: Round Multiple columns in the same Data Set

Luke_C — Wed, 26 Jan 2022 18:19:42 GMT

Hi @Karl_Spratt

You can use a multi-field formula tool, see below:

Re: Round Multiple columns in the same Data Set

gabrielvilella — Wed, 26 Jan 2022 18:26:58 GMT

Hi @Karl_Spratt, here is an example of how to do that creating everything from scratch. It shows one way of creating the average then comparing it to existing data.

Re: Round Multiple columns in the same Data Set

Karl_Spratt — Wed, 26 Jan 2022 18:28:13 GMT

Hi Luke,

how do I create the rounded average for so the formula will work?

Regards,
Karl.

Re: Round Multiple columns in the same Data Set

Luke_C — Wed, 26 Jan 2022 18:30:26 GMT

Hi @Karl_Spratt sorry - post made it seem like it was in your data already.

@gabrielvilella 's solution should have you covered!

Re: Round Multiple columns in the same Data Set

Karl_Spratt — Wed, 26 Jan 2022 18:36:44 GMT

Thanks @gabrielvilella

This is amazing thank you, last question how would I sort the output data so they come in a 1-12 sort ( 1 being next month etc. ) Can you please advise?

Cheers,

Karl.

Re: Round Multiple columns in the same Data Set

gabrielvilella — Wed, 26 Jan 2022 18:42:36 GMT

The cross tab tool should automatically sort the column names ascending, alphabetically or numerically. On my machine I have it from 1-12. Maybe try enabling the AMP engine as I saved this with that on.

Re: Round Multiple columns in the same Data Set

Karl_Spratt — Wed, 26 Jan 2022 18:57:06 GMT

Hi gabrielvilella

Mine is set to - Strange its not sorting into Numeric 1 to 12 , would you know of any other "trick" I can use?

Re: Round Multiple columns in the same Data Set

gabrielvilella — Wed, 26 Jan 2022 19:01:56 GMT

Try inserting a new cross tab tool and configuring it. Or even try pasting the tools on a new workflow. This is not the expected behavior.

Re: Round Multiple columns in the same Data Set

Karl_Spratt — Wed, 26 Jan 2022 21:40:06 GMT

Thanks gabrielvilella that worked. Cheers Karl.

Re: Round Multiple columns in the same Data Set

JoeHughes137 — Wed, 03 Dec 2025 11:58:47 GMT