Creating a row under very specific conditions

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

Datasets

Accepted answers

atcodedog05

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 : )

Sales adjust.yxmd

atcodedog05

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 : )

Sales adjust - 5 product.yxmd

All comments

atcodedog05

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 : )

Sales adjust.yxmd

dgogue

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?

atcodedog05

Hi @dgogue

You would need to adjust the calculation accordingly.

dgogue

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?

atcodedog05

Hi @dgogue

Do you have mutiple rows for A for week 1. Can you share a sample data where you are facing issues.

dgogue

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!

Artifical Made Up Data.xlsx

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703