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

Alteryx designer Discussions

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

Alteryx Build a Loop to See lowest value and limit SUM TOTAL - MACRO

Highlighted

I believe a macro is needed to solve the problem below.

 

I have the following raw table data

The formula for coverage in Months is =INT((inventory+SUM(march:may))/monthly forecast requirement)

 

ItemInventoryMonthly forecast requirementCoverage in monthsMar-19Apr-19May-19
186745230S161725   
1867452353002219   
186745240S602712   
1867452451501815   
1867452509108   
   Total000

 

In Alteryx I need some help designing a kind of loop to solve this problem.

 

The loop would read the complete Column - "Coverage in months" and then pick the first lowest Number it sees. In the example above it would be Row 1 which reads 0. Then the loop should "+1" in the column Mar-19 for that particular Item.

 

Looking again for the formula for Monthly forecast requirement, we can see that even after pasting "1", the coverage in Months will still be 0 for this particular item.

 

So the loop runs again and looks again at the lowest value in the Coverage column and increase quantity by "1".

 

Another condition is the Total sum at the end of each month would be 1000.

 

After running the loop for Mar 19 then the loop would jump to the next column April 19

 

The output should look something like this. The results below are fake numbers

 InventoryMonthly forecast requirementCoverage in monthsMar-19Apr - 19   
186745230S16175100200   
1867452353002218100100   
186745240S60276200100   
1867452451501815350250   
1867452509105250350   
   Total10001000   

 

Thank you for your help.

An example excel sheet is attached

 

Bolide

Hi there,

 

I think I was able to do what you accomplished in an iterative macro. Take a look and tell me if you meant something different.. even if you did, this should give you a good start.

 

Best,

mmenth

Labels