Join % to Quantity and Summarize

Hi everyone, I'm a new Alteryx user and I'm trying to come up with a way to take a list of items by location, match it with a progress report submitted by another team, and then produce an estimate of inventory progress by type of item as well as overall for all items. I've attached the Alteryx workflow I'm trying to work on.

The problems I'm running into are that I can sum the total of items in one worksheet to get a list of how many things there are total, but I can't get a function to work that will multiply the totals by area by the percentage completion for that area, then sum those up and divide the result by the total number of items overall for that category to get a completion %. I previously ran into issues where the null values returned on the join weren't allowing me to do numeric calculations, but flipping the join seems to have fixed that issue.

I've been able to solve this previously using SQL joins and then just using excel to multiple the % complete column by the total # of items per area column, and then doing a sum of the total #s complete / sum of total # of items columns, but this isn't 100% automatic and I can't pass it off to non-technical teams as a standalone tool.

Here's an example of what kind of data I'm dealing with:

The yellow section is one report that I've created using SQL and a list of all the inventory items. In reality this is multiple files, one for each category of items. The red is a rough progress report I get separately from another team. And the blue is what I'm trying to automate.

https://docs.google.com/spreadsheets/d/1XKFP3yaHatVCTA1cZlijkSHVHvI8xMstmH3siTbsfRA/edit?usp=sharing

Example Progress - sample data.yxmd

Reporting

Accepted answers

Aguisande

Hi @Sman26

Here is how the workflow looks (You can download a copy below this post):

The run down (Note that there are 4 branches after the first join, that's because the second one is the same logic (Copy& Paste of the two first, but removing the Group By Clauses in both Summarize Tools to get the All Items Results):

- Reading both data sources, and do some cleaning on the Inventory Completion to be able to use the values

- The join remains as in your example (both data sources by Area)

- Once you got the join results on Area, you must split your data stream: 1 to get SUM of [On Hand] per [Item Type] and the other, to get the actual completions on the Items. That's why I added a formula before the Summarize tool to get [Total Number on Hand] * [Inventory Completion] and the summarize this result by Item Type.

- Having those values, just join them to get the On Hand and the Real Completion per Item Type

- Calculate the desired output dividing Real Completion / Real On Hand and you got the values per Item Type.

At the end, just UNION both partial results into one unique Output

Hope this helps.

Example Progress - sample data.yxmd

Aguisande

What I think is happening is that you're "missing" some incoming values in the JOIN (grab a couple of Browse Tools and connect them to the L and R outputs of your join tool)