topic Set Analysis excluding all but one field in QlikView

Set Analysis excluding all but one field

Tue, 08 Feb 2011 21:35:26 GMT

Hi, I want to use set analysis to calculate one value regardless of any current selection except one field

I have an expression:

=count({1}[Application Id for Counters]) - counts all ids disregarding current selection correctly.

I need to make sure that the only one field [Application Type] influences this count

Thanks

Set Analysis excluding all but one field

Tue, 08 Feb 2011 21:44:39 GMT

Hi,

I haven´t prove it, but you can do something like this:

=count({1+$<[Application Type]={"*"}>}[Application Id for Counters])

Best Regards!

Set Analysis excluding all but one field

Tue, 08 Feb 2011 21:56:07 GMT

does not work.

What * means here? I need to make sure that If I filer on some [Application Type] the count will change.

Set Analysis excluding all but one field

johnw — Tue, 08 Feb 2011 22:41:30 GMT

Perhaps this?

count({1<[Application Type]=P()>} [Application Id for Counters])

The P() says to use possible application types. I think that won't always be the selected application types, though, if other selections narrow the list down further than your selections. So it's not completely ignoring all other selections in that sense. There are more complicated ways around that, but you may not need them, so let's start with the above. If it works for you, no reason to do something more complicated.

Set Analysis excluding all but one field

Tue, 08 Feb 2011 22:42:36 GMT

Is Application Type a string field?

Try:

=count({1<[Application Type]=
 {'$(=if(GetSelectedCount([Application Type])>0,
 GetFieldSelections([Application Type], Chr(39) & ',' & Chr(39)), Chr(42)))'}>}
 [Application Id for Counters])

That will pull all Application types when none are selected or only the selected Application Types. The Chr(39) & ',' & Chr(39) is you put quotes around the values. The quotes are only needed on string fields.

Edit: Changed the * to a Chr(42)

Set Analysis excluding all but one field

johnw — Tue, 08 Feb 2011 22:53:28 GMT

And NMiller gives the more complicated but arguably more correct answer. 🙂

Set Analysis excluding all but one field

Tue, 08 Feb 2011 23:08:56 GMT

Thank you NMiller.

The Application Type is 1 char field.

This expression works correctly only if some [Application Type] is selected. However when there is no selection on this field I need to have a count disregarding this selection ( no filtering on Application Type) - the biggest number. However I am getting 0 with this expression.

Any Ideas?

Set Analysis excluding all but one field

Tue, 08 Feb 2011 23:20:39 GMT

If there are no selections, you should get Application Type = '*', which should show anything.

Put the expression into a table chart, but don't give it a label. When the chart is rendered, you should see the expression with the dollar sign expansion evaluated. When nothing is selected in Application Type, you should see:

count({1<[Application Type]={'*'}>} [Application Id for Counters])

It should be able to handle no selections.

Set Analysis excluding all but one field

Tue, 08 Feb 2011 23:50:14 GMT


The main expression evaluates to "-" when no selection is done,
When I used count({1<[Application Type]={'*'}>} [Application Id for Counters]) just to test it gives me the correct number, which tels me that when there is no selection evaluation of the expression is not correct.
I also was wrong - Application Type is a character field -consists of multiple words. Can that be a problem?Could not see the error

Set Analysis excluding all but one field

Wed, 09 Feb 2011 17:46:23 GMT

Application Type should work whatever the field type is. It could even work for integers, but the quotes are unnecessary and more complicated.

You need to look at the results of your dollar sign expansion. In my sample, with nothing selected, I get ='*' and from your description that expression is working for you when explicitly defined.

Create a chart. Add your expression, but don't give it a label. Make sure no selections are made in Application Type. Copy the cell contents of the expression header and paste it in here.

Set Analysis excluding all but one field

Thu, 10 Feb 2011 19:16:36 GMT

I do not see how I can get '*' , from the expression you gave. the Char(42) does not have quotes around it. Do I need them?

='$(=if(GetSelectedCount([Application Type])>0, GetFieldSelections([Application Type], Chr(39) & ',' & Chr(39)), Chr(42)))'

I am having some trouble evaluating $ expression in the chart header. Do I use only

='$(=if(GetSelectedCount([Application Type])>0, GetFieldSelections([Application Type], Chr(39) & ',' & Chr(39)), Chr(42)))'

in the header chart and no dimension and no expressions?

If I do that I see * without quotes

Set Analysis excluding all but one field

johnw — Thu, 10 Feb 2011 19:57:06 GMT

No, you don't need to add quotes around chr(42). When he says to "Put the expression into a table chart, but don't give it a label", what he means by "the expression" is the expression he gave you, not some portion of the expression. The whole thing. If you do that (see attached), you will see what he shows you when there's no application type selected, including the single quotes. There ARE single quotes in the expression he gave you. They are outside of the dollar sign expansion, '$(=...)'. If you're not getting single quotes, then you probably copied his expression wrong.

If you play with the attached example, you'll see that NMiller's expression (on the top) is returning the right data, or at least the right data as best I understand your requirement. Since it's the only thing in the chart and there's no label, you can also see how it responds to selections of Application Type. If there are any selected, it lists them. If there are none selected, it uses '*'. Either way, it gives the right count.

The expression I provided is second. If you play with the selections, you can see that it does ignore "Other Field" in the sense that if an Application Type is allowed, it counts all records, not just records that match "Other Field". However, it fails in that if an application type is completely disallowed by the other field selection, its records will NOT be counted. This may still match some people's requirements, but seems less likely to than NMiller's expression.

The third chart shows a third possibility. I think this one will be slightly more efficient when nothing is selected. Rather than reducing to searching for '*', it actually removes that part of the set analysis completely. I doubt any performance difference would be noticeable in practice, though.

And hopefully this discussion also makes it clear why it's so helpful to post an example application with questions like this. There's no reason that we should be having this sort of back and forth discussion about what is and isn't right. There's no reason we should be trying to debug your code without even being able to see your code. We should all be looking at the same application, and it should be obvious whether it's right or wrong.

Set Analysis excluding all but one field

Thu, 10 Feb 2011 23:55:07 GMT

thank you