2 Replies Latest reply: Apr 15, 2013 12:52 PM by Michael Solomovich RSS

    Set analysis using date range?

      I have a requirment to count the number of customers that were contacted between two date ranges, I guess this can be done using set analysis however I am having problems with the syntax.  I appreciate any help?

       

      Basically I have a field named CONTACT_MADE which holds date value in the following format 'DD/MM/YYYY'  I need to count how many customers were contacted between two date ranges e.g. between 01/01/2010 and 31/12/2012 ? (note: multiple rows per customer)

       

      I appreciate any advice.

       

      Thank you

        • Re: Set analysis using date range?
          Gysbert Wassenaar

          Easiest to create two variable, i.e vStartDate and vEndDate, and use those in the expression. Make sure the variables contain numeric dates. For example

           

          vStartDate: date#('01/01/2010','DD/MM/YYYY')

          vEndDate: date#('31/12/2010','DD/MM/YYYY')

           

          The expression will then look something like:

           

          count({<CONTACT_MADE={'>=$(vStartDate)<=$(vEndDate)'}>} distinct Customer)

          • Re: Set analysis using date range?
            Michael Solomovich

            For this specific date range:

            count({<CONTACT_MADE={"$(= '>=' & '01/01/2010' & '<=' & '31/12/2012')"}>} distinct customer

             

            In a more generic form, where you can use expressions for dates
            count(<CONTACT_MADE={"$(= '>=' & date(startdate expression) & '<=' & date(enddate expression))"}>} distinct customer

             

            Regards,
            Michael