13 Replies Latest reply on Nov 22, 2013 8:40 AM by philmodjunk

# Conditional incremental calculation

### Title

Conditional incremental calculation

### Post

Hey,

I need help with calculation formula for the field. All the table contains of pre-set 19 records. I have these fields:

base_number, which can be user entered
increment_pos, which is pre-defined in advanced, values: simple numbers, 5, 4, 3, 2, 1, 0 (base number), -1, -2, -3  etc.
number_calc, which is core of the portal i set and need help with calculating (and should calculate the result from base_number, increment_pos and conditional incremental value).

All those 19 records are put in the portal and show values around the base number, so, if base number is 60 (increment_pos is 0), increment by 5, so sequence goes like this:
50 (increment_pos is -2),
55 (increment_pos is -1),
60 (increment_pos is 0),
65 (increment_pos is 0),
70 (increment_pos is 0), etc.

However, there are some conditions. For numbers lower than 50, increment is by 2.5. (there are some more, but i would adjust according to this example). So, for example, later previously mentioned sequence would go like:
47.5 (increment_pos is -3)
45 (increment_pos is -4)
42.5 (increment_pos is -5)
40 (increment_pos is -6)etc.

I used to count it having such condition:
Case (base_number+increment_pos < 50;
but it's not accurate.

• ###### 1. Re: Conditional incremental calculation

Case allows you to write a more complex calculation.

Case(
base < 10, do first step;
base < 20, do second step;
base < 30, do third step;
base < 70, do next step)

• ###### 2. Re: Conditional incremental calculation

Actually, yes, but it's not that easy.

How would you calculate that if base number is 60, and increment_pos is -3, then the result is 47,5?

• ###### 3. Re: Conditional incremental calculation

Guess I do not understand the formula, but

Case(
base < 10, do first step;
base < 20, do second step;
base < 30, do third step;
base = 60, do what you need;
base < 70, do next step)

Build the case low to high since you are using < with the =.
Case(
test1 ; action1;
test2 ; action2;
...
testx ; actionX)

I do not quite understand the action you are taking following the testing.

• ###### 4. Re: Conditional incremental calculation

Maybe PhilModJunk would come up with another solution, because i'm still confused :(

• ###### 5. Re: Conditional incremental calculation

I'm still trying to figure out your examples:

Shouldn't

60 (increment_pos is 0),
65 (increment_pos is 0),
70 (increment_pos is 0),

be:

60 (increment_pos is 0),
65 (increment_pos is 1),
70 (increment_pos is 2),

???

And the first number in this list is the value of Number_Calc?

• ###### 6. Re: Conditional incremental calculation

Yes. increment_pos for base number is always 0. So if base number is 60, then

60 (increment_pos is 0),
65 (increment_pos is 1),
70 (increment_pos is 2),

so, if we add the opposite side it would look like :

47,5 (increment_pos is -3) (the real issue),
50 (increment_pos is -2),
55 (increment_pos is -1),

60 (increment_pos is 0), (base number)
65 (increment_pos is 1),
70 (increment_pos is 2),

Q: And the first number in this list is the value of Number_Calc?

A: No. base_number is always the middle value. So if i use 19 records, base_number would be 10th, 1st record would have increment_pos = -9, last record would have increment_pos = 9.

• ###### 7. Re: Conditional incremental calculation

So you want to specify a value for the base such as 60, and the number of records to get such a list?

So your inputs for this example might be:

Base = 60, number of records (iterations) = 5 to get:

50 (increment_pos is -2),
55 (increment_pos is -1),
60 (increment_pos is 0),
65 (increment_pos is 1),
70 (increment_pos is 2)

If I have that right, you can use one of several options to number the records in a field. I'll call it Sequence. In the above example, it would have the values 1...5.

Let ( [ incPos = Sequence - NumbRecords + ceiling ( NumbRecords / 2 )  - 1;
incVal = If ( BaseNumber < 50 ; 2.5 ; 5 )
] ;
BaseNumber + incPos * incVal
)

But you < 50 example values don't make sense to me:

47.5 (increment_pos is -3)
45 (increment_pos is -4)
42.5 (increment_pos is -5)
40 (increment_pos is -6)

Didn't you mean to post:

32.5 ; -3
35 ; -2
37.5 -1
40 (increment_pos is -0)
42.5 (increment_pos is 1)
45 (increment_pos is 2)
47.5 ; 3

????

Otherwise, I just can't pick the needed calculation details out of your examples to produce something that is consistent with your other example.

• ###### 8. Re: Conditional incremental calculation

ok, ok. I would explain the whole sequence, maybe then it would get more clear.

actually, the higher the numbers, the higher value is for increment. the are 4 incremental values: 0,5; 1; 2,5; 5

Case (base_number   >  50; 5; (50, 55, 60, 65, etc.)
Case (base_number   >  30; 2,5; (30, 32,5, 35, 37,5, etc.)
Case (base_number   >  15; 1; (15, 16, 17, 18, etc.)
Case (base_number  > 0; ,5; (0, 0,5, 1, 1,5, etc.)

So, in this case, if base number is 45, the whole sequence looks like this:

27 (-9)
28 (-8)
29 (-7) (lower than 30, so it's down by 1 from now on, not by 2,5)
30 (-6)
32,5 (-5)
35 (-4)
37,5 (-3)
40 (-2)
42,5 (-1)
45 (0) (base_number)
47,5 (1)
50 (2)
55 (3) (higher than 50, so it's not up by 2,5, but by 5 from now on)
60 (4)
65 (5)
70 (6)
75 (7)
80 (8)
85 (9)

• ###### 9. Re: Conditional incremental calculation

But what determines the number of items in your sequence? Is it a set number or also a value to be specified?

And it seems that you are using a calculated value to determine how that value calculates and thus the snake eats its tail here....

• ###### 10. Re: Conditional incremental calculation

And it seems that you are using a calculated value to determine how that value calculates and thus the snake eats its tail here....

Kinda, and i need a way to get out of here.

Actually, number of items are preset and will never change (and is not determined by calculation). I use table with with by-hand created 19 records (and no options to create more are given) and those -9, -8, -7, etc. values are not seen on layout, they are pre-set in advance, so all those 19 items always have same attributes - 9 positions that are lower than base_number, then 1 base_number, and then 9 positions that are higher than the base number,

base_number may change, but the number_calc (field that is displayed, which is used to calculate appropriate numbers, derived from base_number according to the rules described in previous post and by which the portal is sorted) should always adjust and calculate the appropriate values.

• ###### 11. Re: Conditional incremental calculation

So you always need 19 records, 9, negative, 9 positive and the original base value at + 0.

That need to calculate the result and then calculate it again with a different increment if the calculated result falls in a different value range looks truly weird. I wonder if this isn't a kind of "step function" that replicates a geometric or logarithmic curve. If so, there might be a totally different, algebra based approach that might be used. (I once reverse engineered a table of data being looked up in an old DB and figured out the original math function, then when I used the function to reproduce the values in the table, I found that a portion of the tabular data was not correct...)

When I think that I've got it figured out and do some calculations to check, I keep finding apparent inconsistencies.

I'll point out two values from your last example series:

32.5 - 2.5 = 30 (-6)

that seems to indicate that you are using: Case (base_number   >  30; 2,5;

and

47.5 + 2.5 = 50 (2)

that seems to indicate that you are using: Case (base_number   >  50; 5; (50, 55, 60, 65, etc.)

Either that, or we are supposed to be using the value in blue as the base_number in the case function in order to calculate the next base_number shown in black.

• ###### 12. Re: Conditional incremental calculation

i think i would try to do it a hard way: write a huge "Let (this = that; etc....) statement for all 19 items and see what i could come up with. it may be a huge calculation, but it's still worth doing anyway.

• ###### 13. Re: Conditional incremental calculation

I've got such a calculation started, but keep running aground on possible contradictions in your sample data. If I can clear up those details, I think that I have something that will work. And it does compute all four values using each of the different increments, then I started to set up logic for selecting the correct result and ran into the questions that I asked in my previous post.