community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Challenging Calculation Logic!! Help Needed (Multi Row Formula??)

Meteoroid

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

 

Capture.PNG

 

The formula to apply has a certain rules here. 

Basically....

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

 

 

Thank you so much in advance. 

 

Tearless

Highlighted
Asteroid

Hi Tearless,

 

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.

 

Thanks,

 

Michael

Meteoroid

Thank you for the message. I am not even close to the solution at all... I just thought the multi row tool might be useful, but I'm not even good at using that tool. Thank you anyway!

Asteroid

Hi Tearless,

 

I've made an attempt at what you posed, I'm really not sure if I'm close or not, it's hard to completely understand what you mean but I've tried.

 

Hopefully you can see what I'm trying to do here and maybe it'll help you arrive at the correct answer.

 

Thanks,

 

Michael

Labels