Creating a loop/iterative calculation

[randaya]

Hi,

Can someone help recreate the below excel formula in Alteryx please?

i'd like to create a formula (loop/iteration) where Gross sales = Net Sales + tax but the only number I have is the Net sales and the tax rates. 

	Sample 1	Sample 2	Comments	Excel formula
Net sales	10,000.00	25,000.00	Given input	XXX [Entry]
tax	1,764.71	9,722.22	Calculated (Gross sales * tax brackets)	(Gross sales)*VLOOKUP((Gross sales),tax rates table,tax rate column)
Gross sales	11,764.71	34,722.22	Calculated sum of net sales and tax brackets	Net sales + Tax
	15% rate	28% rate

Tax rates table below for reference:

Gross Sales level	Tax rate
0	10.00%
10,001	15.00%
20,001	25.00%
30,001	28.00%

Thanks in advance!

[jdunkerley79 · Accepted solution]

I attach a solution.

Basically, I compute the equivalent Net Sales Level to the threshold for Gross. Please note that there is overlap in how you have described the system, around boundaries as when you jump up the tax paid jumps up. My approach will assume you pay most tax possible.

You then add a record ID to the sales data and then append the fields to join sets. Next use a sample tool to pick active tax level. You can then compute tax.

[randaya]

Great, thanks! This helps. 

Now to add to the complication, can you take a look at the attached workflow. This is a simple tax calculator but I'd like to build similar to what you've indicated. The tax brackets now here is not as straightforward.

What I build is just the straightforward calculator. Now, I wanted to have the net sales add the tax and calculate the tax based on gross sales (net sales + tax). I've tried what you've suggested but I'm struggling to come up with the formula in case the rate changes. For example, see employee 7. Net sales is 200,000 which is at 29% rate if doing a straight up calculation. However, it will be at 33% if adding the tax on it. 

Thanks in advance!