Round Multiple columns in the same Data Set

[Karl_Spratt]

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

[Luke_C]

Hi @Karl_Spratt 

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

[gabrielvilella]

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. 

[Karl_Spratt]

Hi Luke, 

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

Regards,
Karl. 

[Luke_C]

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

@gabrielvilella 's solution should have you covered!

[Karl_Spratt]

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. 

[gabrielvilella]

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. 

[Karl_Spratt]

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?

[gabrielvilella · Accepted solution]

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.

[Karl_Spratt]

Thanks gabrielvilella that worked. Cheers Karl.