topic Set Analysis Greater Than variable in QlikView

Set Analysis Greater Than variable

Fri, 04 Dec 2009 04:34:31 GMT

I am try to do a simple set analysis and use greater than a variable.

count

({$<[Days in GMRC] = {"> SLA"}>}Distinct [Policy Number])

[Days In GMRC] and SLA are both variables. I can get this to work if I put a hardcoded number where the variable SLA is but that is not dynamic enough for this to work in my case.

Here is the only greater than in the set analysis documentation.

sum( {$<Year = {">1978<2004"}>} Sales )
as above, but now with a numeric search so that an arbitrary range can be specified.

I have tried single quotes no quotes and all other possibilitiesd I couold think of. Does anyone have a solution for this? I think it should be simple and I am just missing something.

Set Analysis Greater Than variable

Fri, 04 Dec 2009 05:53:31 GMT

Your greater than is spot on, it's your variable that is the problem.

({$<[Days in GMRC] = {"> $(SLA)"}>}Distinct [Policy Number])

I'm actually surprised that Days in GMRC is a variable and that is working. I thought only fields were allowed there.

Set Analysis Greater Than variable

Fri, 04 Dec 2009 22:00:05 GMT

Days in GMRC is a field that I created in the script. Thanks, your reply did the trick.

Set Analysis Greater Than variable

Sat, 05 Dec 2009 03:48:06 GMT

This is driving me crazy!

I have used the code above and it works perfectly.

I can make the selection in the field [Document Type] = REN and it shows the numbers I expect to see. I wanted to move the selection of document type in my set analysis so the user doesnt have to make that selection because they dont always know what they are looking for. When I add this to my set analysis the number is way different. What is wrong below?

If(Type = 'Renewals', count({$< [Document Type] = {"REN"}, [Days Prior to Renewal]={"<$(=min(SLA))"}>}Distinct [Item Number1]),

Set Analysis Greater Than variable

Sat, 05 Dec 2009 04:28:07 GMT

You went from (with REN selected in Document Type):

If(Type = 'Renewals', count({$<[Days Prior to Renewal]={"<$(=min(SLA))"}>}Distinct [Item Number1]),

To this:

If(Type = 'Renewals', count({$< [Document Type] = {"REN"},
[Days Prior to Renewal]={"<$(=min(SLA))"}>}Distinct [Item Number1]),

And you're not getting the same result? I don't see anything unusual, but it's kind of hard to see without knowing the data.

Here are a few things I'd check:

Try single quotes are REN. I'm pretty sure that won't help, but I recall there is a difference between the two (I usually use the doubles).
Make sure in your data set it is exactly REN and not "REN " or something.
Get rid of the if and compare only the count portion of the expressions.
Get rid of the distinct and see if the numbers match

Those are just a few things to look at. It's hard without seeing the data. If you could scramble up some data and post an example of it being different, someone could probably figure it out.