Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
satishkurra
Specialist II
Specialist II

Get count in column using SET Analysis

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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.

View solution in original post

16 Replies
swuehl
MVP
MVP

Not sure what you want, maybe

=Count({<ID = {1,3} >} DISTINCT answer)

?

sunny_talwar

May be this:

Interval(Only({<ID = {3}>} answer) - Only({<ID = {1}>} answer), 'D hh:mm:ss')

satishkurra
Specialist II
Specialist II
Author

I need to calculate the average days to complete days

The average date is sum(answer) with id =1

and complete date is Sept 20

I need to get the difference of dates and divide this by Count of All

Hope i'm clear now...

Thanks

Satish

satishkurra
Specialist II
Specialist II
Author

Sunny, i also have ID=1 multiple records in the data.(see below)

How do we need to achieve in this case?

LOAD * Inline [

ID,answer

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

1,2015-03-05 01:21:00

1,2015-04-03 12:01:00

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

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

Thanks

Satish

sunny_talwar

Not I understand, what would be the output using the data you have given above and what is the logic? Can you walk us through step by step?

satishkurra
Specialist II
Specialist II
Author

Hi swuehl

I have data in the below fashion.

LOAD * Inline [

question_id,answer_text_short

1,2015-02-05 13:10:21

1,2015-03-05 13:21:00

2,2015-04-04 13:10:21

3,2015-09-08 12:10:21];

I need to extract the difference of dates from Question_id 1 and Sep 8 and divide by Question ID 1,  Something like below

DIfference of (question_id =1 and Sep8)/QUestion id 1

Thanks

satishkurra
Specialist II
Specialist II
Author

I have data in the below fashion.

LOAD * Inline [

question_id,answer_text_short

1,2015-02-05 13:10:21

1,2015-03-05 13:21:00

2,2015-04-04 13:10:21

3,2015-09-08 12:10:21];

I need to extract the difference of dates from Question_id 1 and Sep 8 and divide by Question ID 1,  Something like below

DIfference of dates count(question_id =1 and Sep8)/QUestion id 1

Thanks

swuehl
MVP
MVP

Maybe

=Interval(

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

,'dd hh:mm')

satishkurra
Specialist II
Specialist II
Author

I'm trying to achieve something like this

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

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