2 Replies Latest reply on Jul 26, 2012 9:17 AM by Nicolas Nadeau

    Conditional MIN

    Nicolas Nadeau

      Hey guys

       

      I want to calculate : how many days it took for a user to "take action" based on his first session.

      In the sample data (see table below) provided, the answer would be : 5 days

       

      In other words, i'd like to do something like this:

      DATEDIFF('day',MIN(DATE),MIN(date) WHERE action > 0)

       

      this formula doesnt work since MIN(date) WHERE action > 0 isnt valid, how should I go about ?

       

      Thx

      N

       

       

      sample data

      useriddateaction
      31812/02/20120
      31812/03/20120
      31812/04/20120
      31812/07/20120
      31812/07/20121
      31812/08/20120
      31812/21/20120

       

       


        • 1. Re: Conditional MIN
          Jonathan Drummey

          Hi Nicolas,

           

          Attached is a sample workbook.

           

          The goal is that we need to calculate the days difference between the date from one row (where action >0) and another row. If we just wanted a datediff between the rows, that's do-able using the regular aggregated functions, or if we wanted to return a value for a certain row then we could do that using an aggregated or non-aggregated function. But to do both at once, we need a table calc.

           

          Here's a table calculation that gets the desired # of days:

           

          IF ATTR([action]) > 0 THEN

               DATEDIFF('day',LOOKUP(ATTR([date]),FIRST()),ATTR([date]))

          END

           

          The calc requires that the userid, date, and action be in the level of detail in the view. The table calc is set with an Advanced... Compute Using  on date, userid, and action, sorted on date/Min/Ascending. I created two different views, one is a simple crosstab and the other being histogram-type view with a a Count of how many userids per days.

           

          Jonathan

          • 2. Re: Conditional MIN
            Nicolas Nadeau

            Table calc for the win once again - thanks for this precise answer !