Check if a certain never exist for a specific field

[#1]

Hello everyone,

I'm trying to check if an value exist in a column. I have the expression :

Avg(

Aggr(

NetWorkDays(

Min(if(Left(matricule,1)='S', date_action)), Max( if([Type de Contact]<>'P91' or [Type de Contact]<>'P92' or [Type de Contact]<>'P93' or [Type de Contact]<>'P98',date_action))),[Numéro de Contrat]))

I'm calculating the average delay for a "numéro de contrat" between the last action of a "matricule" which begin by S and the last action where the "type de contact" is different of P91, P92 ...

The problem is that I want the numéro de contrat where the type de contact is never to P91,P92 ...

For exemple here I have the right delay between 1 oct and 18 nov (35 days) but it's false because this "numero de contrat" is finalised with the P91 the 18 nov also. I only want the delay if P91,P92 ... never exist for this "Numéro de Contrat". So like that I know if the contract is not finalised and since when.

Thank you in advance

[rwunderlich]

Change your max() to use set analysis:

Max({<[Type de Contact] -= {P91,P92,P93,P98}>} [Numéro de Contrat])

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[#3]

Hello Rob,

I made something easier :

Avg(

Aggr(

NetWorkDays(

Min(if(Left(matricule,1)='S', date_action)), Date(Now())),[Numéro de Contrat]))

But I only want the "Numéro de Contrat" where P91,P92 ... never appear for this "Numéro de Contract", and I tried the set analysis and it does the same than my previous if condition.

Thank you

[rwunderlich]

Can you post a sample qvw?

-Rob

[swuehl · Accepted solution]

Maybe

Avg( {< [Numéro de Contrat] = e({<[Type de Contact] = {P91,P92,P93,P98}>})   >}

Aggr(

NetWorkDays(

Min(if(Left(matricule,1)='S', date_action)), Date(Now())),[Numéro de Contrat]))

edit: Maybe add the set expression also to the Min() function

edit 2:  reference: Excluding values in Set Analysis

[#6]

It worked perfectly thank you !

What's the difference between set expression with {} and an if condition ?

[swuehl]

Both operate at different levels:

Logical Inference and Aggregations