2 Replies Latest reply: May 17, 2011 2:23 PM by Bikash Debnath RSS

    Conditions to Set Analysis

      Hello Everyone,

      I want to learn, how we can change the following if...else expression to a set analysis syntax.

       

      sum(if(STAGE='Pending' AND CREATED_DATE<= Today() AND  REVENUE_RECOGNITION_FLAG<>'No' AND OPP_REG_ARCHIVE<>'False' AND IS_PART_ACTIVE=1 AND DESIGN_REG_STATUS<>'Rejected', TOTAL_VALUE))

       

      I would be grateful if someone could please answer it please,

      Thanking you beforehand,

      Bikash

        • Conditions to Set Analysis
          Erich Shiino

          Try this:

           

          sum( {< STAGE={Pending}, CREATED_DATE= {"<= $( =Today())"},  REVENUE_RECOGNITION_FLAG-={No},  OPP_REG_ARCHIVE-={'False'} ,  IS_PART_ACTIVE={1}, DESIGN_REG_STATUS-={ Rejected} > }  TOTAL_VALUE)

           

          I guess the only trick part may be the CREATED_DATE. If it doesn't work, you should created a numeric field on the script to achieve this

          The syntax would be something like:

          LOAD

          CREATED_DATE,

          NUM(CREATED_DATE) AS NCREATED_DATE

          ...  [ THE REST OF YOUR LOAD SCRIPT]

           

          Then, on set analysis it would be:

          sum( {< STAGE={Pending}, CREATED_DATE= {"<= $(=num(Today()))"},  REVENUE_RECOGNITION_FLAG-={No},  OPP_REG_ARCHIVE-={'False'} ,  IS_PART_ACTIVE={1}, DESIGN_REG_STATUS-={ Rejected} > }  TOTAL_VALUE)

           

          Hope it helps,

           

          Erich