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]

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? 

[bates3jr]

Yes, that is correct @gabrielvilella

[bates3jr]

Yes, that is correct 

[gabrielvilella]

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.

            

[bates3jr]

@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.

[gabrielvilella]

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. 

[bates3jr]

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

[bates3jr]

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

[bates3jr]

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.