1 Reply Latest reply: May 17, 2011 11:54 AM by Gerald Stockinger RSS

    firstsortedvalue with set Analysis

    Riccardo Cattermol

      Dear all

      i need your help for a firstsortedvalue function with set analysis.

       

      Goals:

      extract the max date for the records that are between two imput date

       

      Situation :

       

      CODEESITOSTATUSLEVELDESCRDATE
      3660PoorCPR3.3Audit10/09/2007
      3660CPR3.1Awareness Program20/05/2008
      3660GoodCPR3.3Audit

      09/09/2008

       

      VarDataFrom = 01/01/2004

      VarDataTo = 31/12/2007

       

      Expression:

       

      firstsortedvalue( {$<SUPERVIS.DateCreatedokNum={">=$(#VarDataFrom), <=$(#VarDataTo)"}>}

                                        SUPERVISION.DateCreatedokNum,-SUPERVISION.DateCreatedokNum)

       

       

       

      the chart extract always the last date (09/09/2008)  regardless the two input variables.

       

      Please help me.

       

      Best Regards

      Riccardo

        • Re: firstsortedvalue with set Analysis

          Hi,

           

          the reason is the comma in the set analysis. It is an "inclusive or", which means that the date is either bigger than VarDataFrom or smaller than VarDataTo.

           

          Therefore the right formula is

          firstsortedvalue( {$<SUPERVIS.DateCreatedokNum={">=$(#VarDataFrom)<=$(#VarDataTo)"}>}

                                            SUPERVISION.DateCreatedokNum,-SUPERVISION.DateCreatedokNum)

           

          See the textbox in the attached qvw for more information.

           

          Cheers,

          Gerald