SUMIF value is less than or equal to lowest value

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.

All comments

Hey @bates3jr, for the Discounted Price $10 you are only considering the two orders $10 and $9 because you have another discount of $8, so you cannot count the orders for$8 and below, correct?

Yes, that is correct @gabrielvilella

Yes, that is correct

I solved this by using an iterative macro, not sure if it is possible to do this without it. I added some extra scenarios on the data.

@gabrielvilella I'm having trouble recreating this solution with my actual data, which includes some customer info as well as month and year data. Could you explain what each step in your solution is actually doing? I've never used an iterative macro before.

Hey @bates3jr, are you getting any erros or are you just not seeing all the data that you need after the macro? It should't be an issue if you have more columns on the Orders table, for instance. Maybe if you provide some sample data I could understand better what you need.

Hi @gabrielvilella,

I'm not getting errors, I'm just not seeing the data I am expecting. I should have given more background at the start. We get point of sale data that shows how many units a retailer sold at a certain price. We offer rebates at certain prices. Retailers submit rebate requests to us by telling us what product they sold at or below our rebate eligible price. The goal of the alteryx workflow is to compare the point of sale data to their rebate requests to make sure they aren't submitting requests for more than they are eligible to receive.

Here is some sample data with numbers and names changed. In the example below you will see the customer requested a rebate for 593 units of product 10021 at a price of 18.50 but they really only sold 589 units at that price.

Point of Sale Data:

Month	PricingYear	Customer	Price Zone	PRODTYPESID	UNITS SOLD	SELLING_PRICE
Dec	2021	CUSTOMER 1	PRICE ZONE 1	10015	78	15.50
Dec	2021	CUSTOMER 1	PRICE ZONE 1	10015	10	15.95
Dec	2021	CUSTOMER 1	PRICE ZONE 1	10015	4	18.20
Dec	2021	CUSTOMER 1	PRICE ZONE 1	10021	589	18.50
Dec	2021	CUSTOMER 1	PRICE ZONE 1	10021	28	22.50
Dec	2021	CUSTOMER 1	PRICE ZONE 1	10021	81	24.25
Dec	2021	CUSTOMER 1	PRICE ZONE 1	10041	529	15.50
Dec	2021	CUSTOMER 1	PRICE ZONE 1	10041	60	15.95
Dec	2021	CUSTOMER 1	PRICE ZONE 1	10041	16	18.20
Dec	2021	CUSTOMER 1	PRICE ZONE 1	10041	9	24.25

Rebate Request Data:

Month	Year	Price Zone	Distributor	PRODTYPESID	Units Sold at Discount	DISCOUNT PRICE
Dec	2021	PRICE ZONE 1	CUSTOMER 1	10015	78	15.5
Dec	2021	PRICE ZONE 1	CUSTOMER 1	10015	10	15.95
Dec	2021	PRICE ZONE 1	CUSTOMER 1	10015	4	18.2
Dec	2021	PRICE ZONE 1	CUSTOMER 1	10021	593	18.5
Dec	2021	PRICE ZONE 1	CUSTOMER 1	10041	529	15.5
Dec	2021	PRICE ZONE 1	CUSTOMER 1	10041	60	15.95
Dec	2021	PRICE ZONE 1	CUSTOMER 1	10041	16	18.2

Hi @gabrielvilella,

I probably should have provided some more information from the start. My company gets point of sale data from our retailers that shows how many units they sold of our product and at what price they sold them at. We offer rebates to the retailers if they sold these units at or below certain prices. The retailers submit rebate requests to us where they say how many units they sold and what rebate those cases were elgible for. The workflow I want to build in alteryx essentially compares the units they are claiming they sold at or below a certain price to the units they actually sold.

So here is the actual data with some names and numbers switched around. You'll notice this customer claimed they sold 593 units of product 10021 at or below $18.50 but our point of sale data shows they only sold 589 cases at this price. Ideally, in the Requested Rebates table, I would see a column that has "Cases Elgible for Rebate" which would show the amount of cases they sold at or below a certain price and not duplicating any units that were sold at or below the next offered rebate price.

Point of Sale Data:

PRODTYPESID	Customer	Price Zone	Selling Price	Month	Year	Units Sold
10015	CUSTOMER 1	PRICE ZONE 1	15.50	Dec	2021	78
10015	CUSTOMER 1	PRICE ZONE 1	15.95	Dec	2021	10
10015	CUSTOMER 1	PRICE ZONE 1	18.20	Dec	2021	4
10021	CUSTOMER 1	PRICE ZONE 1	18.50	Dec	2021	589
10021	CUSTOMER 1	PRICE ZONE 1	22.50	Dec	2021	28
10021	CUSTOMER 1	PRICE ZONE 1	24.25	Dec	2021	81
10041	CUSTOMER 1	PRICE ZONE 1	15.50	Dec	2021	529
10041	CUSTOMER 1	PRICE ZONE 1	15.95	Dec	2021	60
10041	CUSTOMER 1	PRICE ZONE 1	18.20	Dec	2021	16
10041	CUSTOMER 1	PRICE ZONE 1	24.25	Dec	2021	9

Rebate Request Data:

Month	Year	Price Zone	Customer	PRODTYPESID	Units Sold at Discount	DISCOUNT PRICE
Dec	2021	PRICE ZONE 1	CUSTOMER 1	10015	78	15.5
Dec	2021	PRICE ZONE 1	CUSTOMER 1	10015	10	15.95
Dec	2021	PRICE ZONE 1	CUSTOMER 1	10015	4	18.2
Dec	2021	PRICE ZONE 1	CUSTOMER 1	10021	593	18.5
Dec	2021	PRICE ZONE 1	CUSTOMER 1	10021	28	22.5
Dec	2021	PRICE ZONE 1	CUSTOMER 1	10041	529	15.5
Dec	2021	PRICE ZONE 1	CUSTOMER 1	10041	60	15.95
Dec	2021	PRICE ZONE 1	CUSTOMER 1	10041	16	18.2

Hi @gabrielvilella,

I'm not getting errors I'm just not seeing the data I need. I should have provided some more information from the start. So we basically get two data sets. One data set is the point of sale data that shows how many units a retailer sold and at what price they sold. The second data set is the rebate data that the retailer submits to us. They submit a request for a rebate by telling us how many units they sold at or below the offered rebate price. The goal of this workflow is to show how many units they actually sold at or below the offered rebate without duplicating units that were sold at or below the next lowest offered rebate price.

So in the attached data set you will notice that the customer submitted a rebate for product 10021 claiming 593 units sold at $18.50 but the point of sale data shows they really only sold 589 units. Ideally, the point of sale unit quantities would be brought in next to the claimed units so we could compare whether or not they sold as many units as they claimed they did at a specified price tier.

Example Data.xlsx

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?

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.

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.

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

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.