topic Re: Help need with expression in QlikView

Help need with expression

ramyasaiqv — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I’m trying to count the number of orders which have Type= ‘BCT’ and Status=’Accepted’ (no ECT status at all) and Type=’ECT’ and Status=’Open’ or ‘-‘.

Please can someone help me in writing the expression? Attached sample qvw and excel for reference.

Expected Result:

Count 3(BUS, DESC and PTZ)

Thanks,

Ramya

Re: Help need with expression

Anil_Babu_Samineni — Wed, 20 Sep 2017 04:50:14 GMT

May be this?

Count({<Type = {'BCT', 'ECT'}, Status = {'Open','Accepted'}>} Order)

Re: Help need with expression

antoniotiman — Wed, 20 Sep 2017 06:03:57 GMT

Hi,

try this

=Count({<Order=P({<Type={'BCT'},Status={Accepted}>})*
P({<Type={'ECT'},Status={Open}>+<Order={"=Count(Order) = 1"},Type={'BCT'},Status={Accepted}>})>} DISTINCT Order)

Regards,

Antonio

Re: Help need with expression

jonathandienst — Wed, 20 Sep 2017 06:25:37 GMT

Like this

=Count({<Type = {'BCT'}, Status = {'Accepted'}> + <Type = {'ECT'}, Status = {'Open'}>} Order)

By '-', you mean null, I assume. Then maybe this:

=Count({<Type = {'BCT'}, Status = {'Accepted'}> + <Type = {'ECT'}, Status = {'Open'}> + <Type = {'ECT'}, Status -= {'*'}>} Order)

(You have no null statuses in your data, so I could not test this)

Re: Help need with expression

antoniotiman — Wed, 20 Sep 2017 07:19:57 GMT

=Count({<Order=P({<Type={'BCT'},Status={Accepted}>})*
P({<Type={'ECT'},Status={Open}>+<Order={"=Count(Order) = 1"},Type={'BCT'},Status={Accepted}>})>} DISTINCT Order)
&Chr(10)&
Concat({<Order=P({<Type={'BCT'},Status={Accepted}>})*
P({<Type={'ECT'},Status={Open}>+<Order={"=Count(Order) = 1"},Type={'BCT'},Status={Accepted}>})>} DISTINCT Order,'-')

Re: Help need with expression

hari8088 — Wed, 20 Sep 2017 08:15:40 GMT

Hi,

Try this.

count(if(Order='BUS'or Order='DESC'or Order='PTZ', if(Type='BCT' and Status='Accepted',Order))) +

count(if(Order='BUS'or Order='DESC'or Order='PTZ', if(Type='ECT' and Status='Open',Order)))

Re: Help need with expression

ramyasaiqv — Wed, 20 Sep 2017 21:44:38 GMT

Thank you Antonio.

Re: Help need with expression

ramyasaiqv — Wed, 20 Sep 2017 21:45:07 GMT

Thank you for the response Anil.

Re: Help need with expression

ramyasaiqv — Wed, 20 Sep 2017 21:45:56 GMT

Thank you for the response Jonathan.

Re: Help need with expression

ramyasaiqv — Wed, 20 Sep 2017 21:46:19 GMT

Thank you for the response hari krishna.