The topic for today’s post comes from Brian Wulfe, Online Marketing Manager for Richlund Ventures, Inc. Brian and I have been having forum discussions at our Strategy and Support Center, and he’s come up with several scenarios for business rules to calculate discounted prices. One quick note: All the rules I’m showing below use the FORMATCURRENCY function to make sure the final value is…well, formatted correctly; that is, it’s a number with no currency symbol, and exactly two digits to the right of the decimal point.
Excluding Products from the Discount
Here’s Brian’s first scenario:
We currently apply the business rule FORMATCURRENCY(0.95 * $currentprice) to the product price field to compute a 5% discount. How could we exclude a selection of products from that rule?
Assuming the items you want to exclude were in a list called “No Discount”, I would try the following:
FORMATCURRENCY(IF(ISINLIST("No Discount",$model), 1.0, 0.95) * $currentprice)
This is the same as what you have, except instead of always multiplying the price by 0.95, we calculate a multiplier using the following logic:
- if the item’s model (a.k.a., “sku”) is in the list called “No Discount”, the multiplier is 1.0 (i.e., no discount)
- otherwise, the multiplier is 0.95
Discounting Only Items from a Certain Manufacturer
Here’s the next scenario:
I am trying to use the IF function to apply discount only to a single set of products and leaving everything else the same. I would like to apply this discount by looking in the Manufacturer column and finding “XYZ” and then reducing the corresponding prices by 10%.
This one is just a variation of the first; instead of applying the discount to all items except certain ones, the discount is only for certain items. So:
FORMATCURRENCY(IF($manufacturer = “XYZ”,0.90,1.00) * $currentprice)
Multiple Discount Rates
Brian’s last scenario:
I want to apply a blanket discount of 7% to all skus except for a particular look up list called “No Discount.” In addition, I would like to specify unique discounts to certain categories such as “Outdoor Living” or “Appliances > Ice Makers,” but I need a “wild card” as there are several different subcategories within each.
Here’s what I cam up with:
FORMATCURRENCY(
SELECTCASE(
ISINLIST("No Discount",$model),1.0,
CONTAINS($merchantcategory,"Outdoor Living"),0.90,
CONTAINS($merchantcategory,"Ice Makers"),0.95,
0.93)
* $currentprice)
This is still the same pattern: we have a logic block that computes a multiplier, which is then multiplied by the price. However, since there are several different cases we need to consider, we’re not using the IF function. We’re using SELECTCASE. Inside the SELECTCASE we’re using the CONTAINS function to match the category values.
Another possibility, if you have lots of categories with different discounts, is to use a lookup list for that as well. You would have each discounted category in the “name” boxes, and the respective discounts in the “value” boxes. If you name the list “Category Discounts,” then your rule could look like this:
FORMATCURRENCY(
IF(ISINLIST("No Discount",$model),1.0,
IFBLANK(LOOKUP("Category Discounts",$merchantcategory),"0.93"))
* $currentprice)
That’s all for now. Thanks, Brian, for sharing your ideas with us! And you readers out there, let me hear from you.
