Get count in column using SET Analysis

[satishkurra]

Hi

I have a data in the below fashion:

LOAD * Inline [

ID,answer

1,2015-02-05 01:01:00

2,2015-04-04 02:02:00

3,2015-09-08 03:03:03 ];

I need to extract the count of the difference of dates from ID 1 and 3

Thanks

Satish

[satishkurra]

Trying to achieve something like this

sum(date({<question_id={1}>}answer_text_short)- date(september 8th))

/count({<question_id={1}>}answer_text_short)

[swuehl]

Just using the same approach than in my previous post:

=Interval(

Sum({<question_id = {1}>} ($(=num(Only({<question_id = {3}>} answer_text_short),'#.','.',',')) - answer_text_short))

/ Count({<question_id = {1}>} answer_text_short)

,'dd hh:mm')

[satishkurra]

Thanks

1 last question,

In the above expression, i need to hard code question_id =3 as September 8 date.

Thanks

Satish

[swuehl]

=Interval(

Sum({<question_id = {1}>} Makedate(2015,9,8) - answer_text_short))

/ Count({<question_id = {1}>} answer_text_short)

,'dd hh:mm')

[satishkurra]

Thanks, But this is giving error in expression...

[satishkurra]

Hi I corrected the syntax error , but the output is "200 10:44"

We need to get the no. of days here...

[swuehl · Accepted solution]

Well, '200 10:44' is equal to 200 days, 10 hours and 44 minutes (using the format code 'dd hh:mm' to interval() function).

If you just want the number of days as a number, just use Round(), Floor() or Ceil() instead of the Interval() function.