topic Re: Get count in column using SET Analysis in QlikView

Get count in column using SET Analysis

satishkurra — Mon, 07 Dec 2015 16:20:38 GMT

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

Re: Get count in column using SET Analysis

swuehl — Mon, 07 Dec 2015 16:22:59 GMT

Not sure what you want, maybe

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

Re: Get count in column using SET Analysis

sunny_talwar — Mon, 07 Dec 2015 16:24:57 GMT

May be this:

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

Re: Get count in column using SET Analysis

satishkurra — Mon, 07 Dec 2015 16:29:41 GMT

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

Re: Get count in column using SET Analysis

satishkurra — Mon, 07 Dec 2015 17:00:43 GMT

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

Re: Get count in column using SET Analysis

sunny_talwar — Mon, 07 Dec 2015 17:03:38 GMT

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?

Re: Get count in column using SET Analysis

satishkurra — Mon, 07 Dec 2015 17:05:07 GMT

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

Re: Get count in column using SET Analysis

satishkurra — Mon, 07 Dec 2015 17:06:06 GMT

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

Re: Get count in column using SET Analysis

swuehl — Mon, 07 Dec 2015 17:22:23 GMT

Maybe

=Interval(

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

,'dd hh:mm')

Re: Get count in column using SET Analysis

satishkurra — Mon, 07 Dec 2015 17:23:47 GMT

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)

Re: Get count in column using SET Analysis

satishkurra — Mon, 07 Dec 2015 17:24:46 GMT

Trying to achieve something like this

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

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

Re: Get count in column using SET Analysis

swuehl — Mon, 07 Dec 2015 17:34:36 GMT

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')

Re: Get count in column using SET Analysis

satishkurra — Mon, 07 Dec 2015 17:37:50 GMT

Thanks

1 last question,

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

Thanks

Satish

Re: Get count in column using SET Analysis

swuehl — Mon, 07 Dec 2015 17:40:33 GMT

=Interval(

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

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

,'dd hh:mm')

Re: Get count in column using SET Analysis

satishkurra — Mon, 07 Dec 2015 17:50:51 GMT

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

Re: Get count in column using SET Analysis

satishkurra — Mon, 07 Dec 2015 17:54:25 GMT

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

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

Re: Get count in column using SET Analysis

swuehl — Mon, 07 Dec 2015 17:57:48 GMT

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.