SUMIF value is less than or equal to lowest value

[bates3jr]

Hi All,

Fairly new to alteryx and I've been stuck on an issue for several days now. I have 2 data sets, one data set shows discounts on products and the other data set shows # of units sold at specific prices. There can be multiple discounts for any one product. What I need to do is bring in the number of units sold at or below each price but I can't double count units. For example, say I sold 5 units at $10, 5 units at $9, 5 units at $8 and 5 units at $7. Let's say in the discount table for this product, there were discounts at $10 and $8. I need the units sold column for $10 to show 10 units, and the units sold for $8 to show 10 units. I can't figure out how to not double count units for each discounted price interval. For example, there are 20 units sold at or below $10 but because there is a 2nd discounted price at $8, I only want to sum units between $10 and $8. I'm currently showing 20 units at or below $10 and 10 units at or below $8 implying we sold 30 units but really it should equal 20.

Data Set 1:

Product	Discounted Price
Product 1	$10
Product 1	$8

Data Set 2:

Product	Selling Price	Units Sold
Product 1	$10	5
Product 1	$9	5
Product 1	$8	5
Product 1	$7	5

Goal:

Product	Discounted Price	Units Sold at or Below Discounted Price
Product 1	$10	10
Product 1	$8	10

[gabrielvilella]

Thank you for explaining that. Two questions, do you have more months on the same database? Is there more than one price zone for the same customer? 

[bates3jr]

Yes I do have more months in the same database and no, a customer can only be assigned to 1 price zone but multiple customers can roll up to 1 price zone.

[gabrielvilella · Accepted solution]

I had to change the fields within the macro to match what you have on your dataset and add a few more keys to make sure it is comparing the correct data. See if this is what you are looking for. 

[bates3jr]

@gabrielvilella that appears to have worked! Thank you so much, you are a life saver!

[gabrielvilella]

Happy to help @bates3jr! 🙂

[bates3jr]

Hello @gabrielvilella,

There is some added complexity to this problem you solved a couple weeks back that I was previously unaware of. So in addition to the criteria previously mentioned, I need to join a "MinCaseQty" column to the rebate request data. Basically, in order for a customer to get a rebate, they need to have sold a minimum amount of units (Cases) for each order. The Point of Sale data is at the order level. So I only want to join the cases from the point of sale data if the order quantity was greater than or equal to the "MinCaseQty" in the rebate data.

As an example, let's say the customer had 4 orders for 1 product all at or below the required rebate price. Quantities for each order were 20, 10, 5 and 2. MinCaseQty for this rebate price was 10, so we should ignore the orders that were only for 5 and 2 units.

Any idea on how to add this logic to the previous iterative macro? I had to create 2 columns in the discount input: PriceConfigID and MinCaseQty. 

[gabrielvilella]

If I got it correctly from what you said, I believe what you need is just a filter before the summarize. See the attached file. 

[bates3jr]

Hi @gabrielvilella 

This seemed to work! Might've been overthinking it lol

Thanks!!