Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Need Help with Calculations under three different conditions

8 - Asteroid

Hello, I need help to calculate discount under three different conditions. Please find below sample data.

 

Table 1Current PriceCurrent Discount
Customer A            10.00                  (4.00)
Customer B            10.00                  (4.00)
Customer C            10.00                  (4.00)
Customer D            10.00                  (4.00)
Customer E            10.00                  (4.00)

 

Table 2Price Condition Price Increase 1Condition Rate 1Price Increase 2Condition Rate 2Price Increase 3Condition Rate 3
Customer A12.0%2.0%4.0%2.0%5.0%2.0%
Customer B22.0%25.0%4.0%25.0%5.0%25.0%
Customer C12.0%3.0%4.0%3.0%5.0%3.0%
Customer D 2.0% 4.0% 5.0% 
Customer E32.0%2.0%4.0%4.0%5.0%

5.0%

 

Here is the logic of how to process for each "Price Condition":

 

New Price = Current Price * (1+ Price Increase) for all situations. So this one is easy.

 

New Discount will be different based on the following:

 

If Price Condition = 1, then do following
Check if Current Discount is zero/null. If so, New Discount is zero/null
If Condition Rate is same or greater than Price Increase, New Discount is same as Current Discount
If Condition Rate is less than Price increase, then New Discount = Current Discount + (Condition Rate - Price Increase)*Current Price

If Price Condition = 2, then do following
Check if Current Discount is zero/null. If so, New Discount is zero/null
New Discount = (Current Price times Price Increase times Condition rate times -1)+(Current Discount)

If Price Condition = 3, then do following
Check if Current Discount is zero/null. If so, New Discount is zero/null
New Discount = Current Discount *(1 + Condition Rate)

If Price Condition = 0 or null
New Discount = Current Discount

 

Here is desired output table:

OutputCurrent PriceCurrent DiscountNew Price 1New Discount 1New Price 2New Discount 2New Price 3New Discount 3
Customer A            10.00                  (4.00)                  10.20                (4.00)                10.40                  (4.20)                        10.50                  (4.30)
Customer B            10.00                  (4.00)                  10.20                (4.05)                10.40                  (4.10)                        10.50                  (4.13)
Customer C            10.00                  (4.00)                  10.20                (4.00)                10.40                  (4.10)                        10.50                  (4.20)
Customer D            10.00                  (4.00)                  10.20                      -                  10.40                        -                          10.50                        -  
Customer E            10.00                  (4.00)                  10.20                (4.08)                10.40                  (4.16)                        10.50                  (4.20)

 

9 - Comet

Hi 

 

The Output does not match with the conditions that you have provided. However tweaking 2 parts of the discount conditions gives the desired output. I have created the workflow based on the Output.

 

Workflow:-

bhrmitra_0-1590648850936.png

 

Output:-

bhrmitra_1-1590648881018.png

 

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

 

Thanks,

Abhra Mitra

Alteryx Partner
Alteryx Partner

@anamik72 , Here is another alternate solution to your problem. I hope this serves your purpose.

Sapna Gupta
Labels