analytics - Spotfire date difference using over function -

i have following data set:

item  ||  date       || client id || date difference     ||  12/12/2014 || 102       ||      ||  13/12/2014 || 102       || 1 b     ||  12/12/2014 || 141       ||  b     ||  17/12/2014 || 141       || 5 

i calculate difference in years between 2 dates when client id same. expression can use in calculated column value?

update

hi

this intended values calculated. table has approximately 300,000 records in no particular order. have sort physical table before using formula? used example found, actual file has no item column. client id, , date of transaction. again help!

clientid    date        days  102         2014.12.12  0  102         2014.12.13  1  141         2014.12.12  0  141         2014.12.17  5  123         2014.12.01  0  123         2014.12.02  1  123         2014.12.04  2 

edit 2015.07.15

got it, if want difference last customer-date pair. expression give table you've listed above. spacing readability:

datediff('day',   first([date) on (intersect([clientid], previous([date]))),   [date] ) 

---

edit 2015.07.13

if want reduce can accurately aggregate [days], can surround above expression if(). i'll add spacing make more readable:

if(     [date] = min([date]) on intersect([clientid], [item]),      datediff( 'day',          min([date]) on intersect([clientid], [item]),          max([date]) on intersect([clientid], [item])     )     , 0 ) 

in english: "if value of [date] column in row matches earliest date [itemid] , [clientid] combination, put number of days difference between first , last [date] [itemid] , [clientid] combination; otherwise, put zero."

it results in like:

item    clientid    date        days       102         2014.12.12  1       102         2014.12.13  0 b       141         2014.12.12  5 b       141         2014.12.17  0 c       123         2014.12.01  2 c       123         2014.12.02  0 c       123         2014.12.03  0 

warning filters may break calculation. example, if filtering based on [date] and, above table example, filter out dates before 2014.12.13, sum([date]) 7 instead of 8 (because first row has been filtered out).

---

you can use spotfire's over functions @ data points common ids across rows.

it looks you've got 2 rows per client id , item id, helps out! use following formula:

datediff('day', min([date]) on intersect([clientid], [item]), max([date]) on intersect([clientid], [item])) 

this give column number of days difference between 2 dates in each row:

item    clientid    date        days       102         2014.12.12  1       102         2014.12.13  1 b       141         2014.12.12  5 b       141         2014.12.17  5