2 Replies Latest reply: Jul 7, 2016 6:23 AM by stefano crosina RSS

    Creating a new field from nested IF expression

    stefano crosina

      Hi,

       

      I have a question concerning the creation of a new field in Sense.

      I have created a master-item in which I compare a ratio value (numerator/denominator) to a 4-steps range (different for each ratio) and based on this comparison I assign a string value of High, Medium-high, Medium-low and Low to each of them. Now I would like to have this new list of string-values as a field inside my script.  How can I create a field value based on this master-item?

       

      for example:

       

      ratio    high     medium-high     medium-low     low          indicator     

      0.2     1               0.6                     0.4            0.1             low         

      0.5     1               0.8                     0.4            0.05         medium-high

      0.9     0               0.4                     0.8              1              low

       


      Just for being clear I have used a multiple if statement in my formula:


      IF([scale type]='direct',

      (IF((ratio) >= 0 and (ratio) <= ("low"), 'low',

      IF((ratio) > ("low") and (ratio) <= ("medium-low"), 'medium-low',

      IF((ratio) > ("medium-low") and (ratio) <= ("medium-high"), 'medium-high',

      IF((ratio) > ("medium-high") and (ratio) <= ("high"),'high'))))),

       

      (IF((ratio) >= 0 and (ratio) <= ("high"), 'high',

      IF((ratio) > ("high") and (ratio) <= ("medium-high"), 'medium-high',

      IF((ratio) > ("medium-high") and (ratio) <= ("medium-low"), 'medium-low',

      IF((ratio) > ("medium-low") and (ratio) <= ("low"),'low))))))