Dynamic List Formula

bfolliard

Hello Alteryx Community,

I have a specific problem that I couldn't find in the other topics. I have a simple data set that I am applying simple formulas to (in this example summing). However, There are several inputs that determine what would need to be summed. In the below example, the result would sum each of the entities in teh given list (eg List1 would be entiy 1 (50) + entity 2 (25) + entity 3 (75) + entity 4 (25) = 175. See my data set and various inputs below:

DATA SET:

Entity	State	Amount
1	AL	50
2	AR	25
3	IL	75
4	IL	25
5	AL	125

STATE INPUT

State	Grouping
AL	List1
AR	List3
IL	List2

LIST 1

Entity

1

2

3

4

LIST2

Entity

1

2

3

LIST3

Entity

1

3

5

DESIRED RESULT:

List	Amount
List1	175
List2	150
List3	250

To take this one step further, I am looking for a way for the process to be dynamic enough so that if a new "List" was added, it would automatically be incorporated without having to copy/paste the steps to account for it. I have a workbook where each of the lists is a different tab so I was thinking I could get the list of tabs but don't know where to go after that. I have been struggling with this for a while. Any ideas? Thanks in advance for any assistance you're able to provide!

kelly_gilbert

I wonder if you could perhaps union the list/entities into one dataset? Something like this:

List	Entity
List 1	1
List 1	2
List 1	3
List 1	4
List 2	1
List 2	2
List 2	3
List 3	1
List 3	3
List 3	5

Then, you could simply join that to the State/Amount dataset on the entity ID.

To automate creation of the unioned list/entity dataset, you could use a Dynamic Input tool (see this article for instructions). Note, the format/schema for each tab in your workbook needs to be exactly the same for the Dynamic Input tool to work. If that's not the case, then you'll need to follow the second half of the linked article to create a macro.