Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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).
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
Sure, don't you need to divide by something, like count(distinct OrderNo) ?
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