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

# Conditional MIN

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

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

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