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.
I'm trying to calculate the field "Dollar Amount" using formula that I have in each cell.
Data is grouped by Data ID and each data of a group is sorted by effective date
The formula to apply has a certain rules here.
1. Benefit is calculated by multiplying % with base dollar amount
2. For second effective date, the first benefit, which is calculated above, has to be deducted and % has to be multiplied.
3. For third effective date, if the date is same with the previous one, the same logic (#2) will be applied
4. For fourth effective date (if there is any), if the date is same with the previous one, the same goes here (logic #2). However, if the date is later than the previous ones, the sum of benefit (1,2,3) have to be deducted and % has to be multiplied.
The logic is simple, but I don't know if there is any way to handle this calculation in Alteryx.
If I use VBA.. it would be simple.. just apply the calculation logic as many as the number of record in each group using if... statement..
But.. .since I'm a newbie to Alteryx... I'm just trying to solve everything in Alteryx so I can learn as much as possible.
Please let me know if there is any way to handle this......
This does sound challenging and after an inital attempt I realised it was a little more difficult than I first anticipated. If I get some time later on today I'll maybe have a stab at it but you'll probably need a few Formula tools and the main tool you're looking for is the Multi-Row Formula tool which allows you to specify multiple rows within the same field.
Hopefully this points you in the right direction, you are closer to the logic than I am so you might get the result sooner.