alteryx Community

# Alteryx IO Discussions

Customize and extend the power of Alteryx with SDKs, APIs, custom tools, and more.
SOLVED

## Multifield formula Complexity

8 - Asteroid

Hi Experts,

Stuck in a complex situation where I have to use same formula in multiple field with some changes in the value. Please help me to solve this.

The first table is containing the data from 2018 to 2022 in below format -

 Max Date Min Date Diff Column Month 31-01-2018 01-01-2018 31 Jan_2018 28-02-2018 01-02-2018 28 Feb_2018 31-03-2018 01-03-2018 31 Mar_2018 30-04-2018 01-04-2018 30 Apr_2018 31-05-2018 01-05-2018 31 May_2018 30-06-2018 01-06-2018 30 Jun_2018 31-07-2018 01-07-2018 31 Jul_2018 31-08-2018 01-08-2018 31 Aug_2018

second table has some data- including formula tool

 Allocation Started Month Allocation End Month 31-01-2018 28-02-2018 31-03-2018 30-04-2018 31-05-2018 30-06-2018 31-07-2018 30-08-2018 02-01-2018 20-04-2018 44082.8 42082.8 44082.8 44082.8 0 0 0 0 14-05-2018 13-08-2024 0 0 0 0 0 256865 256865 256865 14-08-2019 13-08-2024 0 0 0 0 0 0 0 0

Single formula will applied for all column but the difference is [Max Date], [Min Date] and [Diff Column] will change according to the first table.

formula for 31-01-2018 field --- IF [Allocation start month]>[Max Date] then 0, elseif [Allocation end month]<[Min Date] then 0 elseif [Allocation start month]>=[Min Date] and [Allocation start month]<=[Max Date] then [TCV] else [TCV]*[Diff Column]

7 REPLIES 7
8 - Asteroid

22 - Nova

Hi @AS

It is not very clear. What is TSV and can you provide us with an expected output?

8 - Asteroid

To answer your question [TCV] would be the another column where I'm copying the Amount or doing calculation. But my question is divided into 2 part

1. How 1st table data rows can be bring in the formula (as you can see 2nd table column names are same as rows of first table)

2. how I can implement same formula in every field

The first table is containing the data from 2018 to 2022 in below format -

 Max Date Min Date Diff Column Month 31-01-2018 01-01-2018 31 Jan_2018 28-02-2018 01-02-2018 28 Feb_2018 31-03-2018 01-03-2018 31 Mar_2018 30-04-2018 01-04-2018 30 Apr_2018 31-05-2018 01-05-2018 31 May_2018 30-06-2018 01-06-2018 30 Jun_2018 31-07-2018 01-07-2018 31 Jul_2018 31-08-2018 01-08-2018 31 Aug_2018 and so on

 TCV Allocation Started Month Allocation End Month 31-01-2018 28-02-2018 31-03-2018 30-04-2018 31-05-2018 30-06-2018 31-07-2018 30-08-2018 44082 02-01-2018 20-04-2018 44082.8 44082.8 44082.8 44082.8 0 0 0 0 256865 14-05-2018 13-08-2024 0 0 0 0 0 256865 256865 256865 34342 14-08-2019 13-08-2024 0 0 0 0 0 0 0 0

formula for 31-01-2018 field --- IF [Allocation start month]>[Max Date] then 0, elseif [Allocation end month]<[Min Date] then 0 elseif [Allocation start month]>=[Min Date] and [Allocation start month]<=[Max Date] then [TCV] else [TCV]*[Diff Column]

22 - Nova

Hi @AS

It would be something like this. Please check the formula to get accurate values

Hope this helps : )

8 - Asteroid

Thank you so much. You are a true savior.

8 - Asteroid

Thanks

22 - Nova

Happy to help : ) @AS

Cheers and have a nice day!