I have some data, Item Number and Price, and im looking to created a calculated column, Discounted Price, as shown below:
| Item # | Price | Discounted Price |
|---|---|---|
| TEST-001 | 34.00 | |
| TEST-002 | 73.00 | |
| TEST-003 | 54.00 | |
| TEST-004 | 23.00 | |
| TEST-005 | 99.00 | |
| TEST-006 | 141.00 |
I want to use a control for the user to apply the discount in the visual - Straight forward enough - I originally created a ‘Discount’ parameter and added a 0-100 slider as a % discount control.
WHat I would like to do is use pre-defined ‘discount codes’ instead of this slider. For example:
DISCOUNT CODE 1 = 20% if Item is either TEST-002 or TEST-003
DISCOUNT CODE 2 = 5% any item
DISCOUNT CODE 3 = 8% any item
I had created a single parameter e.g. $DISCOUNT, and control which had DISCOUNT CODE 1, DISCOUNT CODE 2, DISCOUNT CODE 3 as multi-select values in a drop-down list, and then another calc field for the new discounted price, something like:
ifelse(
in('DISCOUNT CODE 1', $DISCOUNT) AND in({Item No}, [TEST-002, TEST-003]), {Price} * 0.80,
in('DISCOUNT CODE 2', $DISCOUNT), {Price} * 0.95,
in('DISCOUNT CODE 2', $DISCOUNT), {Price} * 0.92,
0)
What I want to do is stack these discounts - so if a user selects DISCOUNT CODE 2 and DISCOUNT CODE 3 for example, it will show as giving a (1-0.05) * (1-0.08) = 12.6% discount.
Currently im unable to stack with the current calculated field, it just meets the first condition and applies that.
Thinking it may be a better idea to have individual paramters per discount code, and perhaps calculate total discount in another field first possibly?
If anyone has some ideas on the best way to approach this I would be very grateful!