topic Set Analysis with Greater than and Less than in QlikView

Set Analysis with Greater than and Less than

tmumaw — Fri, 08 Oct 2010 15:33:38 GMT

Thought I had the syntax down, but I guess I don't. Need a little help.

Thanks Thom

sum({<{"len[Cancel Reason] > 0, [Cancel Reason <>{14}"}>} [Non Accepted Sales])

I want to sum Non Accepted Sales if the length of my cancel reason is greater than 0 and cancel reason not equal to 14.

Set Analysis with Greater than and Less than

Fri, 08 Oct 2010 15:43:00 GMT

I think is something like this:

sum({$<[Cancel Reason]={len(Cancel Reason) > 0}, [Cancel Reason]=-{14}>} [Non AcceptedSales])

Set Analysis with Greater than and Less than

Fri, 08 Oct 2010 15:50:16 GMT

I no tested.....the len part i think is wrong.....i will try and post again!!!!

Set Analysis with Greater than and Less than

pover — Fri, 08 Oct 2010 15:53:53 GMT

Try something like this

sum({$<[Cancel Reason] -= {14} * {"=len([Cancel Reason])<0"}>} [Non Accepted Sales])

sum({$<[Cancel Reason] = {"=[Cancel Reason] = 14 and len([Cancel Reason])>0"}>} [Non Accepted Sales])

You always have to put the field={} in set analysis and then the set that you want to filter within {} and if the search is dynamci it should always begin with a equal sign {"= expression "}.

Regards.

Set Analysis with Greater than and Less than

tmumaw — Fri, 08 Oct 2010 16:33:48 GMT

I'm looking for cancel reason code to be equal to anything but '14' or a space, because a space is a good sale.

Set Analysis with Greater than and Less than

pover — Fri, 08 Oct 2010 16:43:40 GMT

Change the second expression to

sum({$<[Cancel Reason] = {"=[Cancel Reason] <> 14 and len([Cancel Reason])>0"}>} [Non Accepted Sales])

The first expression should still remove the 14's since in set analysis the -= excludes values like the <> in a search expression. What I don't know about the first expression is how it does the intersection of the removing the value 14 and the null or empty Cancel Reasons.

Regards.