topic filtering on any value in QlikView

filtering on any value

Anonymous — Mon, 21 Nov 2011 14:50:05 GMT

Hi Guys,

I have question ,

I want to filter the shipments that are late, but are not caused by our company. I have a comment field in qlikview wherin we describe the what the reason of delay is, e.g. 'problems with customs in Beijing' or 'Dammage goods'. If the field is empty, then the delay is our fault.

Now, I want to translate this in my gauge chart, my expression below is incorrect:

sum(if(NetWorkDays(Date,ArrivalDate)<=TransitTime, 1,0) and [PO NO.: & Comments]>0) / Count(DISTINCT OrderNo)

How can I filter on comments that contain any text value?

Thanks in advance!

Isam

filtering on any value

swuehl — Mon, 21 Nov 2011 17:41:21 GMT

Maybe you want to check the length of the field value:

and len([PO NO.: & Comments])>0)

If you could have spaces in that fields, you may want to apply an additional trim() first:

and len(trim([PO NO.: & Comments]))>0)

But I think your if-statement is somewhat incorrect (the and is not part of the if-condition), not sure, but maybe like this

sum(if(NetWorkDays(Date,ArrivalDate)<=TransitTime and len(trim([PO NO.: & Comments]))>0 , 1,0)

/ Count(DISTINCT OrderNo)

Hope this helps,

Stefan

Re: filtering on any value

Anonymous — Tue, 22 Nov 2011 09:01:20 GMT

Hi Stefan,

Again thanks you for your help!! I really appreciate it . I've attached my qlikview file.

The expression does not seem to work. I get a performance of 68.400%

Hope u can help.

Regards,

iSam

filtering on any value

swuehl — Tue, 22 Nov 2011 09:46:32 GMT

Where do you get the number 68.400% from?

If I put above expression (after changing to field name Comments and adding one closing bracket after the sum), I get something like 12%. (No selections made).

Re: filtering on any value

Anonymous — Tue, 22 Nov 2011 11:25:37 GMT

Hi stefan,

This is what I get when using this expression:

sum(if(NetWorkDays(Date,ArrivalDate)<=TransitTime and len(trim(Comments))>0, 1,0))

sorry for all the nagging

thanks in advance!

isam

Re: filtering on any value

swuehl — Tue, 22 Nov 2011 11:41:47 GMT

Sure, don't you need to divide by something, like count(distinct OrderNo) ?

Re: filtering on any value

Anonymous — Tue, 22 Nov 2011 12:19:10 GMT

You abosolutly right. I can't believe that I missed the last part of the expression. I was so focused with your expression, that I forgot to add the last part

My apologize for overlooking the last part of the script!

Thanks alot for your help! I really really really appreciate it.

Regards,

Isam