Hi , I am trying to put a formula in Alteryx for tiered charges.
Basically the formula below is from excel. How can i replicate to Alteryx?
IF(C2<=250, B2*0.2%, IF(AND(C2>250,C2<=750),B2*0.195%, IF(AND(C2>750, C2<=1500), B2*0.19%, B2*0.185%)))
Where C2 is a running total trade value
and trade value is B2. I am trying to find the total charge 3.084
I have simplied a table to show
Trade value | Running Total | Charge |
50 | 50 | 0.1 |
100 | 150 | 0.2 |
300 | 450 | 0.585 |
120 | 570 | 0.234 |
200 | 770 | 0.380 |
200 | 970 | 0.38 |
50 | 1020 | 0.095 |
600 | 1620 | 1.11 |
1620 | 3.084 | |
Trade value | Rate | |
More than 250 | 0.20% | |
Between 250 and 750 | 0.195% | |
Between 750 and 1500 | 0.190% | |
Above 1500 | 0.185% |
Hey @clara777,
Pretty interesting Excel Formula. First thing to note is the syntax for a Excel IF is the same as an Alteryx IIF statment. Second in Alteryx you refer to columns with column names within square brackets. Finally you will have to times numbers by numbers not by percentages so they will have to be converted. I ended up with this:
IIF([Running Total]<=250, [Trade value]*0.002,
IIF([Running Total]>250 AND [Running Total]<=750,[Trade value]*0.00195,
IIF([Running Total]>750 AND [Running Total]<=1500, [Trade value]*0.0019, [Trade value]*0.00185
)))
Although I am not sure how you get your final result of 3.084 applying the rules you made:
Example workflow attached.
The community has some quick and easy videos on formulas and the Formula Tool here https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons/label-name/Writing%20...
Any questions or issues please ask
Ira Watt
Technical Consultant
Watt@Bulien.com
Ah thank you @chukleswk ! It makes sense now 😄
Thanks for all ur help. I will try out!!