Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Creating a row under very specific conditions

dgogue
7 - Meteor

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

7 REPLIES 7
atcodedog05
22 - Nova
22 - Nova

Hi @dgogue 

 

Here is how you can do it.

Input:

atcodedog05_0-1628612294989.png

 

Workflow:

atcodedog05_1-1628612321346.png

 

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

dgogue
7 - Meteor

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
22 - Nova
22 - Nova

Hi @dgogue 

 

You would need to adjust the calculation accordingly.

dgogue
7 - Meteor

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
22 - Nova
22 - Nova

Hi @dgogue 

 

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

dgogue
7 - Meteor

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!

atcodedog05
22 - Nova
22 - Nova

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

Labels