topic Set analysis : Dates comparaison in QlikView

Set analysis : Dates comparaison

Thu, 26 Jul 2012 09:08:12 GMT

Dear all,

I uploaded data in a QV tables :

- 'Calls' with the following fields : C_call_id, C_creation_date, C_finish_date

I created 1 QV object :

- a simple table (T1) with the following dimensions : C_call_id, C_creation_date, C_finish_date

In the table T1 I added the following set analysis as expression

SA1 : =Count({<C_creation_date=C_finish_date>}C_call_id)

I want to get the whole callid that have creation date equal to finish date

Unfortunatly, it doesn't work : I get no line at all in my table T1

Could you please tell me how I can correct my set analysis to get what I want ?

Thank you

Regards

Pascal

Re: Set analysis : Dates comparaison

Thu, 26 Jul 2012 09:13:35 GMT

try like:

Count( if( C_creation_date=C_finish_date,C_call_id,0 )

This is asuming that both the date fields are not date and time fileds.

Best of luck!

Regards

Re: Set analysis : Dates comparaison

Sokkorn — Thu, 26 Jul 2012 09:17:09 GMT

Hi Pascal,

Not sure this one can do your job. But let try

1. Count({<C_creation_date = {"=$(=C_finish_date)"} C_call_id)

2. IF(C_creation_date=C_finish_date,COUNT(C_call_id))

Regards,

Sokkorn

Re: Set analysis : Dates comparaison

Thu, 26 Jul 2012 13:33:53 GMT

Hello,

Sorry but it doesn't work.

I'm surprising because it systematically gives me 1 (for the whole Callid) even if the creation date and the finish date are different.

That's certainly, as you mean, because my two fileds are dates.

Thank you

Regards

Pascal

Re: Set analysis : Dates comparaison

CELAMBARASAN — Thu, 26 Jul 2012 13:49:41 GMT

Hi,

Try this

=Count({<C_call_id={"=C_creation_date=C_finish_date"}>}C_call_id)

Hope it helps

Re: Set analysis : Dates comparaison

Thu, 26 Jul 2012 14:05:58 GMT

Hello,

1. Sorry but the first solution doesn't work (I get no lines).

2. The second solution works : it gives 1 for every callid where the creation date is equal to the finish date but... since it not a set analysis the total is not equal to the sum of '1' occurences (I get a '-' instead). It is really important for me to work with a set analysis because I need to get the total number of calls with same dates later in my QV application.

Thank you

Regards

Pascal

Re: Set analysis : Dates comparaison

a_mullick — Thu, 26 Jul 2012 15:32:49 GMT

Hi,

Have a look at the attached solution. It doesn't use set analysis, which I don't think will work in this scenario: a call creation date can match many call finish dates on different rows: the row based solution gives coherent results.

Thanks,

Azam

UPDATE:

=======

Thinking about it some more, a set based analysis could be achived like this:

Create a 4the field in T1 called C_call_duration, which is the difference in days between C_creation_date and C_finish_date, then you can select the set where C_call_Duration is 0:

count ( {$ <C_call_duration = {0}> } C_call_id)

Re: Set analysis : Dates comparaison

Sokkorn — Fri, 27 Jul 2012 11:48:49 GMT

Hi Pascal,

I got your point. AGGR() function may do this job. Below is the alternative solution

1. SUM(IF(C_creation_date=C_finish_date,COUNT(C_call_id)))

2. SUM(AGGR(IF(C_creation_date=C_finish_date,COUNT(C_call_id)),C_creation_date))

3. SUM(IF(C_creation_date=C_finish_date,1,0))

That will be easy, if you can share your app. We look into it together to find a good solution.

Regards,

Sokkorn