This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Upgrading Server to version 22.1? We’ve enabled the faster AMP engine by default. Read about how these system settings changes might affect you.
We’re aware of an intermittent issue with our My Alteryx login and are actively working to have the issue corrected. If you run into an error when logging in, please try clearing all cookies or accessing the community on a different browser. Thank you for your patience!
I'm having some issues designing a workflow to automate a "first in first out" accounting calculation. For the sake of this example, we'll use a loan in a foreign currency.
Assume I am a Canadian citizen who lends you out money in USD on various occasions, and you pay me back on various occasions. Now for the sake of lending, since you are my good friend, I don't charge you interest, and I just ask you to pay me pack in the same currency I lent you money in (USD). However on my end, I do want to calculate the amount I gained or lost as you pay me back. To do so I want to calculate the difference in the rate of exchange multiplied by the amount. However since multiple debts occur, I'll use the rate of exchange of the first debt until it is totally paid and then the rate of exchange of the next debt until it is fully paid and so on and so forth.
I've shown how to manually calculate it in excel, but I'm having a particular hard time transferring this to alteryx in a way that could handle a very large amount of transactions automatically.
My issues comes with figuring out how I can create a dynamic calculation that considers the right exchange rate (like a running total until it passes the total amount of the first debt).
I am running into a problem when we have multiple debts that are later repaid by the same payback. Any idea on how we could solve this? I was trying to adjust the extra, but I'm realizing we might actually need multiple payout lines for each amount to account for the ROE of each debt.
I've spent a few minutes trying things and I'm at a loss on this one. There's likely a way to do it, but I'd look into an iterative macro -- someone else will likely need to chip in here for advice on that, I'm still a novice :)
Great question and you could definitely use an iterative macro whereby you line up the payments and distribute the debt across them one by one (The iterative part would be the left over payments). It's probably easier to go that way rather than the other.
If you want to play with this type of solution, check out the Weekly Challenge #14 as it deals with distributing to warehouses via priority (I remember it, because it was in the very first certification exam a few years ago).
I had a go at creating a macro, but before I knew had done it without... take a look at the attached, it might not be intuitive at first as it's not looking at it in a procedural manner....