We have extended our Early Bird Tickets for Inspire 2023! Discounted pricing goes until February 24th. Save your spot!

Alteryx Designer Discussions

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

Need help on a formula for tiered calculation

clara777
5 - Atom

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 valueRunning TotalCharge
50500.1
1001500.2
3004500.585
1205700.234
2007700.380
2009700.38
5010200.095
60016201.11
1620 3.084
   
   
Trade value Rate 
More than 2500.20% 
Between 250 and 7500.195% 
Between 750 and 15000.190% 
Above 15000.185% 

 

4 REPLIES 4
IraWatt
17 - Castor

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
)))

 

IraWatt_1-1662980703860.png

Although I am not sure how you get your final result of 3.084 applying the rules you made:

 

IraWatt_0-1662980693206.png

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 

 

chukleswk
11 - Bolide

@IraWatt, this would be because the last row (1620) is the final running total and the final charge (3.084) is a summation of all  of the charges before it. If what you're looking for is the last row then you will need to use the Summarize tool.

 

The attached workflow uses @IraWatt's formula.

Tiered calculation.PNG

IraWatt
17 - Castor

Ah thank you @chukleswk ! It makes sense now 😄

clara777
5 - Atom

Thanks for all ur help. I will try out!!

Labels