topic Re: Choice criteria in set analysis in QlikView

Choice criteria in set analysis

pirotfab — Thu, 31 May 2018 12:13:01 GMT

Hello,

You find the table with the infos

I want to get the time "OK" and the time "KO" for the dimension Test

The conditions: if T YPE = ESSAI then "KO3, if CODE = T100 then KO otherwise OK

For that I put these formulas =

Expression OK : sum({<TYPE-={'ESSAI'},CODE-={'T100'}>} TPS)

Expression KO : sum({<TYPE={'ESSAI'},CODE={'T100'}>} TPS)

The result

My wish is to obtain 24h on the sum of the two expressions.

Is it possible to pass only with the set analysis to realize this table or should I use one if nested moreover?

Thanks for your feedback

Cdlt

Re: Choice criteria in set analysis

sunny_talwar — Thu, 31 May 2018 15:44:21 GMT

May be change KO to this

Sum({<TYPE = {'ESSAI'}>+<CODE = {'T100'}>} TPS)

Re: Choice criteria in set analysis

pirotfab — Thu, 31 May 2018 15:55:21 GMT

Hello,

Thanks for your feedback

the result with your expression

i want to get this

thanks

Re: Choice criteria in set analysis

sunny_talwar — Thu, 31 May 2018 16:04:01 GMT

Try this for OK

=sum({$-(<TYPE={'ESSAI'}>+<CODE={'T100'}>)}TPS)

Re: Choice criteria in set analysis

pirotfab — Fri, 01 Jun 2018 07:09:34 GMT

Thank you Sunny

Can you explain this expression pleas ?

Re: Choice criteria in set analysis

pirotfab — Fri, 01 Jun 2018 08:01:12 GMT

Hi Sunny,

I'm trying to adapt this expression on my work app. But it doesn't word.

do you Have an idea ?

the result

i want

Thanks

Re: Choice criteria in set analysis

sunny_talwar — Fri, 01 Jun 2018 08:57:16 GMT

From what I understand the problem is TYPE because it is all null.... is it possible to replace nulls in type field to be replaced by blanks in the script like this

LOAD TPS,

POSTE,

CODE,

TEST,

If(Len(Trim(TYPE)) = 0, ' ', TYPE) as TYPE,

LIGNE

FROM

(ooxml, embedded labels, table is Feuil1);

Re: Choice criteria in set analysis

pirotfab — Fri, 01 Jun 2018 09:26:39 GMT

here is the result on my work app

tps ouv (s) is expression OK and Tps hors ouverture is expression KO

I want

thanks for you help

Re: Choice criteria in set analysis

sunny_talwar — Fri, 01 Jun 2018 11:06:42 GMT

And what is the expression for Tps ouv (s)?

Re: Choice criteria in set analysis

pirotfab — Fri, 01 Jun 2018 12:14:33 GMT

Tps ouv (s) =sum({$-(<typeln={'POSTEVT'},ofessai={'ESSAI'}>+<%code_type_evt={'FERME','MAP','T402','T403','T401','T405'}>)}duree)

Tps hors ouverture =sum({<typeln={'POSTEVT'},%code_type_evt={'FERME','MAP','T402','T403','T401','T405'}>+<ofessai={'ESSAI'}>}duree)

Do you think that it is better if in the champs "TYPE" I put 1 if ESSAI and 2 if not ESSAI

Thanks

Re: Choice criteria in set analysis

sunny_talwar — Fri, 01 Jun 2018 12:21:47 GMT

May be try this

If(Len(Trim(POSTE)) > 0, Sum(TOTAL <CODE> {$-(<typeln={'POSTEVT'},ofessai={'ESSAI'}>+<%code_type_evt={'FERME','MAP','T402','T403','T401','T405'}>)} duree))

Re: Choice criteria in set analysis

pirotfab — Fri, 01 Jun 2018 12:32:33 GMT

it's not ok.

The problem is the expression Tps ouv (s).

that's what makes me two POSTE line

Re: Choice criteria in set analysis

sunny_talwar — Fri, 01 Jun 2018 12:36:16 GMT

But that is the expression I am asking you to change with this one

If(Len(Trim(POSTE)) > 0, Sum(TOTAL <CODE> {$-(<typeln={'POSTEVT'},ofessai={'ESSAI'}>+<%code_type_evt={'FERME','MAP','T402','T403','T401','T405'}>)} duree))

Re: Choice criteria in set analysis

pirotfab — Fri, 01 Jun 2018 12:41:34 GMT

thanks

It's ok

but if I add a dimension here is the result

Re: Choice criteria in set analysis

sunny_talwar — Fri, 01 Jun 2018 12:43:47 GMT

Well, unless I know or see the whole thing... I won't be able to give you an expression which will work in all cases... the expression is not straight forward because of the way your data is...

Re: Choice criteria in set analysis

pirotfab — Fri, 01 Jun 2018 12:47:59 GMT

ok ,

thank you for you help

i advance very well with you

thanks sunny

Re: Choice criteria in set analysis

sunny_talwar — Fri, 01 Jun 2018 12:51:16 GMT

No problem at all