I am using a data set from syndicated data that can break down customer data aggregated by class of trade. We do not receive data on one type of class of trade but can infer the number by subtracting the available class of trade sales from the Total Sales. I am looking for a way to do this calculation in Alteryx over a large number of observations.
Sample Data Set
Time Period | Product Name | Class of Trade | Sales
Week 1 | Product A | All Customers | $200
Week 1 | Product A | Customer A | $100
Week 1 | Product A | Customer B | $20
In the above example we do not get data from customer C, but the implied sales of customer C is $80 (to sum to All Customers sales of $200).
I am looking for a way to add a row that would Subtract Customer A and B Sales from All Customers and give me a backed out observation for Week 1 | Product A | Customer C. To add to this, some products are only sold at Customer A and Customer C so it wouldnt always be subtracting Customer B (because sales would be 0). Sorry if this is confusing, I am unable to share more specific data.
Thanks Alteryx Community
Solved! Go to Solution.
Hi @dgogue
Here is how you can do it.
Input:
Workflow:
1. I am maintaining a list of required customer rows in a text input tool.
2. Using summarize tool to get list of weeks and products.
3. Using append tool to create all possible combinations.
4. Using formula to trim $ and convert to number.
5. Using join multiple to do outer join and get existing sales.
6. Soting to get blank value at bottom of combination.
7. Using multi-row formula to calculate the missing one.
8. Using sort tool to sort as per required.
Hope this helps : )
thanks this is great. if i have more classes of trade, ie Customer D, E, F do i just increase the number of rows accordingly in the multi row tool?
Hi again,
I am super close here but for some reason the output of the multiple joins is creating duplicates of each record for each customer. so for the Week 1, there are multiple records for customer A. any thoughts there?
Hi @dgogue
Do you have mutiple rows for A for week 1. Can you share a sample data where you are facing issues.
I've created a fake data set.
In the real data set of 12 weeks of data, 700 different products. There are also different fields with product attributes like size, flavor etc. that i need carried over.
All products are missing the "Dollar" customer. All products have data on Total, Grocery, Gas, Pharmacy and Walmart. Any of null values for Grocery, Gas, Pharma, and Walmart are 0. "Dollar" sales will always be the difference between Total-Sum(gas,grocery,pharmacy,walmart)
I need to add a row for every product for every week that has the "Dollar" store sales and brings in the product attributes associated with that product.
In the fake data set, i left out the product attributes for simplicity. I also made an example of what the end data should look like.
Thanks!
Hi @dgogue
It seems like your input data and expected output data doesnt have same number of distict customers. Input has 4 and expected has 5. I have built it for 5.
Please check and let me know.
Hope this helps : )