Escalating Discounts Calculation

[mattdunn]

Hi,

I have been struggling with this. I have a database of items for sale. Each item can have a constant discount, so "Widget A" always gets a discount of 10%, this of course is easy to calculate:

Price * Discount (.1)

But what if I wanted "Widget B" to have a 10% discount from 0-100 units, 15% from 101-500 units, 20% from 500+ units? Discount becomes a dynamic variable based on Quantity. Does anyone have any tips for this kind of calculation?

Edited July 23, 2009 by Guest

[mweiss]

How about this?

A calculated field DiscountRate, defined by:

Case(Quantity < 101; 0.10 ; Quantity < 501 ; 0.15 ; 0.20)

Then a second calculated field, Discount, defined by:

Discount = DiscountRate * Price

[mattdunn]

Cool, it's always more simple than I think.

If I could ask for one more bit of advice. What if those quantities (100 and 500) and those discounts (10, 15, 20) differ from product to product? "Widget C" could have a 30% discount only take effect at 10000+ units. I was thinking that the discounts and associated quantities could live in a different related table, that way each item could have any number of discounts. Any way to make that Case statement more portable/reusable?

[comment]

Not calculation - lookup from a table of Discounts. See:

http://fmforums.com/forum/showtopic.php?tid/197517/

[mattdunn]

I will take a look at that.

Thanks for the feedback.