1 Reply Latest reply: Oct 2, 2012 2:03 PM by Deepak Kurup RSS

    Scripting problem.

      How do I write an expression with conditions?

       

      I have two date fields (referred and qualified). I want to write an experssion that says:

       

      if the referred date field has an entry and the qualified date field has an entry, then do sum(qualified date) - sum(referred date) / count(qualified date - refererred date).

       

      Example:

       

       

      ConsumerReferred DateQualified Date
      A1/1/20111/3/2011
      B1/12011
      C1/1/20111/5/2011

       

      So only A and C would be selected, giving : ((1/3/2011) - (1/1/2011)) / (2)

        • Re: Scripting problem.
          Deepak Kurup

          hi,

           

          I would suggest the following method

           

          At script level

           

           

          Load Consumer ,Referred Date, Qualified Date,if( len(trim([Referred Date])) >0,'Y','N') as Ref_Ind,

          if( len(trim([Qualified Date])) >0,'Y','N') as Qualifed_Ind

           

          from Table;

           

           

          At epression it would be

           

           

          sum({<Ref_Ind={"Y"},Qualifed_Ind={"Y"}>}qualified date) - sum({<Ref_Ind={"Y"},Qualifed_Ind={"Y"}>}referred date) / count({<Ref_Ind={"Y"},Qualifed_Ind={"Y"}>} qualified date - refererred date)

           

           

          I hope the above code helps.

           

           

          Deepak