topic Is it possible to use Set Analysis with alphanumeric criteria ? in QlikView

Is it possible to use Set Analysis with alphanumeric criteria ?

oxiofrt — Tue, 09 Mar 2010 21:06:44 GMT

Hi,

Set Analysis is really useful.

However, I cannot succeed in creating a formula which would take into account a restriction on an alphanumeric (or date) expression

For example, the following expressions do not work :

= count({$<NOM_COMMUNE ={ >= "ZZZ"}>} distinct ID_INTERVENTION)

= count({$<DH_DEBUT_INTER = {">= $(Dateheure_d)"}>} distinct ID_INTERVENTION)

Knowing that :

Dateheure_d variable is defined by the following expression : makedate(ANNEE_D, MOIS_D, JOUR_D) & ' ' & maketime(HEURE_D,0,0)

DH_DEBUT_INTER is a date with format DD/M/YYYY HH24:I:SS

ANNEE_D, MOIS_D, JOUR_D and HEURE_D are integers that are constituting the following date : 01/01/2009 00:00:00

Dateheure_d is correct as the following syntax (with a IF), works fine :

= count( distinct if(DH_DEBUT_INTER >= $(Dateheure_d),ID_INTERVENTION))

Can you help ?

Thanks a lot for your help !

Is it possible to use Set Analysis with alphanumeric criteria ?

sparur — Wed, 10 Mar 2010 09:20:45 GMT

It's possible.

But field and restriction which are used in Set Analysis must have equal date format, that is if your field DH_DEBUT_INTER has date format as 'DD/MM/YYYY hh:mm:ss' that your variable Dateheure_d should has the same date format.

Is it possible to use Set Analysis with alphanumeric criteria ?

oxiofrt — Wed, 10 Mar 2010 11:47:54 GMT

I can't get it works.

You will find herejoined an application containing an example.

Can you help ?

Thanks a lot !

Is it possible to use Set Analysis with alphanumeric criteria ?

sparur — Wed, 10 Mar 2010 11:55:44 GMT

change your 3rd expression to:

count({<DH_DEBUT_INTER = {">=$(DT_1)"}>} distinct ID_INTERVENTION)

Is it possible to use Set Analysis with alphanumeric criteria ?

sparur — Wed, 10 Mar 2010 11:58:14 GMT

or if you don't want to use a variable try that:

count({<DH_DEBUT_INTER = {">=$(=DATE_1)"}>} distinct ID_INTERVENTION)

Is it possible to use Set Analysis with alphanumeric criteria ?

oxiofrt — Wed, 10 Mar 2010 12:10:44 GMT

Great it works, nut how can I combine the test for 2 dates.

As in the herjoined application ?

Is it possible to use Set Analysis with alphanumeric criteria ?

Wed, 10 Mar 2010 12:17:33 GMT

Hi,

here you go.

Good luck!

Rainer

Is it possible to use Set Analysis with alphanumeric criteria ?

sparur — Wed, 10 Mar 2010 12:18:50 GMT

If you mean date interval that try something that:

count({<DH_DEBUT_INTER = {">=$(DATE_1)<$(=DATE_2)"}>} distinct ID_INTERVENTION)

Is it possible to use Set Analysis with alphanumeric criteria ?

oxiofrt — Wed, 10 Mar 2010 13:29:52 GMT

Great !!!!

Thanks a lot.