INCREMENTALLY DECREASE A THRESHOLD VALUE TILL A CONDITION IS MET

[Shahas]

Hi Everyone,

This is a repeat question as i couldn't get an answer earlier. Below is the workflow stage I'm at.

After this stage, I have to check for the sum of New Delta. If it's above 20k, the threshold of 'abs([Delta Adj])' has to be decreased 500 every time until the sum of New Delta is below 20k. 

I believe we need to use a iterative macro here. As I haven't worked using macro, would be grateful to anyone who can assist on the same.

Thanks in advance

[JamesCharnley]

@Shahas If you want to use an iterative macro I can share the general logic here but you'd need to change the template to match your schema. But the macro will effectively check whether or not that value is >= 20000, and if it is then it will continue to iterate. Once that condition is not met, your value will leave the other output.

[Shahas]

heyy @JamesCharnley  Thanks for the response.

Correct me if I'm wrong here, but the filter will just be checking for row by row values, and if they are above 20k, it performs the loop condition.

What I would like to have is to check whether the sum of new Delta is above 20k and if so, delta adj threshold should be reduced incrementally by 500 till the sum of New delta becomes less than 20k.

Hope its clear

[Pang_Hee_Choy]

if it contains sensitive data, you can mock some sample data, and illustrate how it goes.

if not, it just waste all of us time for guessing.

you also can see that no body want to answer your question, it wasted time to guess your data, rebuild data and etc.

here is my understand, the 20k is limit on total, and -500 for all rows adj delta if exceed.

another guess, minimum of adj delta is 0, 

the another guess, iteration stop adding if reach to 0.

add iteration column for stop iteration increase if met criteria.

sum new delta, append back, reduce adj delta, remove columns for iteration.

[Shahas]

heyy @Pang_Hee_Choy The dataset looks like below :

Here, as u can see, Delta Adj values are filtered to be less than 20k each. After this step is done, I need to check the sum of New Delta, and if its above 20k, Delta Adj value threshold of 20k has to be reduced by 500 to 19500 and again check for the sum of New Delta. This process has to be repeated till the sum of New Delta reaches below 20k.

FYI : (NEW DELTA = DELTA ORIGINAL + DELTA ADJ)

Hope this brings a bit of clarity

[Pang_Hee_Choy]

@Shahas did my attached file help?

and based on your data.

Total first 2 line already exceed . so it should reduce till negative value?

i required more detail mock sample in excel/table and explanation, not screenshot. for raw data, 1st round, and the final outcome.

For example:

Ori

Ori Delta	Adj Delta	New Delta
20000	2400	22400
8000	2000	10000

total new delta exceeds 20k, reduce each adj delta for 500

after 1st round macro

Ori Delta	Adj Delta	New Delta
20000	1900	21900
8000	1500	9500

still over 20k, continue till

Ori Delta	Adj Delta	New Delta
20000	-4100	15900
8000	-4500	3500

[Shahas]

@Pang_Hee_Choy Apologies for the late reply.

The logic implemented in the example is exactly what I require. You can use the attached mock data :

Original delta	Delta Adj	New Delta
21000	4500	25500
15000	3200	18200
4566	2500	7066
3245	1456	4701
5740	4000	9740
9500	3000	12500
2250	2000	4250
1245	3256	4501
3500	1000	4500

[Pang_Hee_Choy · Accepted solution]

here the update version. add summary in case you want to see.

[Shahas]

@Pang_Hee_Choy That seems to work.. Thank u so much. really appreciated.

[Shahas]

@Pang_Hee_Choy just a small request though..

Correct me if I'm wrong. Here I believe we are reducing values of Delta Adj by 500 in each row...

instead of that, I just need the threshold to be reduced by 500

at first, the threshold of Delta Adj is kept as < 20000. So only those rows which are having values less than 20k are selected. then sum of New Delta is checked and if it is above 20k, threshold has to be reduced by 500. So then only rows which are less than 19500 are selected and so on..

Hope its clear