Set analysis : Dates comparaison

[#1]

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

[#2]

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

R

[Sokkorn]

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

[#4]

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

[CELAMBARASAN]

Hi,

Try this

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

Hope it helps

[#6]

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

[a_mullick]

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)

[Sokkorn]

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