1 Reply Latest reply on Aug 21, 2018 4:39 AM by Jim Dehner

    Changing result of a Calculated Field

    Alessandro Passera

      Hi Everyone,

       

      I have a variable in my dataset called: [Subscription Type].

       

      The variable contains strings. Here some examples:

       

      month_1999

      year_2488

      month_0000

      month_new

       

      I want to extract the last 4 characters and transform them in a decimal number, such as 19,99, 24,88.

       

      So far, I created a Calculated field that allow me to do so:

       

      FLOAT(RIGHT([Subscription Type Code],4))/100

       

      The problem is that by using this Calculated field, I get some values equal to 0 or Null.

       

      How can I add to my Calculated Field an IF statment so that if the result is 0 or Null, the result of the Calculated Field will be a value from another variable (in this case [Order Total]).

       

      Many Thanks in Advance!

       

      Alessandro

        • 1. Re: Changing result of a Calculated Field
          Jim Dehner

          I would prefer to see the data in a twbx workbook

           

          but try this

             if   ifnull(        FLOAT(RIGHT([Subscription Type Code],4))/100,0) =0 then (Order total) else  FLOAT(RIGHT([Subscription Type Code],4))/100 end

           

           

          Jim

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          1 of 1 people found this helpful