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

    Conditional incremental calculation

    ultranix

      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.

           Please help me solve this issue.

        • 1. Re: Conditional incremental calculation
          TKnTexas

               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
            ultranix

                 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
              TKnTexas

                   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
                ultranix

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

                • 5. Re: Conditional incremental calculation
                  philmodjunk

                       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
                    ultranix

                         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
                      philmodjunk

                           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
                        ultranix

                             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
                          philmodjunk

                               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
                            ultranix
                                 

                                      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
                              philmodjunk

                                   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
                                ultranix

                                     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
                                  philmodjunk

                                       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.