We have a fairly repeatable process that I'm trying to automate using Alteryx, and the last step is to create summarized tables of the underlying data. The process to create summaries depends on user selections. For the example data below, what we have is a list of products, general sales information about the products, and then fields for each location (both for the company and for the competitors) and an indicator that shows whether or not that product is sold there. Here is some sample data:
| Product | Units | Customers | Sales | Location 1 | Location 2 | Location 3 | Competitor Location 1 | Competitor Location 2 | Competitor Location 3 | Competitor Location 4 | Competitor Location 5 |
| Apples | 100 | 10 | $150 | No | Yes | No | Yes | Yes | No | No | No |
| Oranges | 50 | 15 | $200 | No | No | Yes | Yes | Yes | No | Yes | No |
| Grapes | 75 | 8 | $125 | Yes | No | No | Yes | Yes | No | No | No |
| Peaches | 125 | 21 | $325 | Yes | Yes | No | No | Yes | No | Yes | Yes |
| Pears | 60 | 21 | $225 | No | Yes | No | No | No | Yes | No | Yes |
| Watermelons | 45 | 9 | $90 | Yes | Yes | Yes | Yes | Yes | Yes | No | Yes |
The goal with the summarized tables is to compare our locations to the competitor locations, but we are not always comparing the same locations. Our end users have the ability to select one or more of our locations, plus one or more of the competitor locations, and the report shows a summarized table of each of the comparisons (so if they select one location and one competitor location, they will receive one summarized table, but if they select two company locations and two competitor locations, they will receive four summaries - each of our locations against each competitor locations).
Here is what the summarized table would look like for a single company location against a single competitor location (Location 1 vs Competitor Location 1):
| | Products | Units | Customers | Sales |
| Products Sold at Location 1 and Competitor Location 1 | 2 | 120 | 17 | $215 |
| Products not sold at either Location 1 or Competitor Location 1 | 1 | 60 | 21 | $225 |
| Products sold at Competitor Location 1 and not sold at Location1 | 2 | 150 | 25 | $350 |
| Products sold at Location 1 and not sold at Competitor Location 1 | 1 | 125 | 21 | $325 |
Where I'm struggling is how to make this dynamic enough to create multiple summarized tables, depending on the user selections. How can I set this up to loop through each of the user selections (for both the company locations and the competitor locations) and create a summarized table for each iteration?
The other item that will come into play is that we don't always have the same number of summarized columns. In this case, we are counting the number of products, and then summing the other three columns, but our end users don't always want to see all four summarized columns. Is there a way to not summarize, for example, the number of customers, if our end user doesn't want that? Thanks
