topic Re: Ignoring Certain Fields using Variables in Set Analysis in QlikView

Ignoring Certain Fields using Variables in Set Analysis

lucasdavis500 — Wed, 25 Nov 2020 16:16:04 GMT

Is it possible to ignore certain fields using set analysis with a variable? I currently have some fields I would like to ignore in my expressions, so that my end user can view %'s based on selections.

Currently I have expression similar to this:

COUNT({<Some Set Analysis>}Pkey)

Untouched, this would be 100%. When the user makes selections, I want the top piece to be affected by selections, but not the bottom. I know I can do something like this for the bottom piece:

COUNT({<Field1=, Field2=, Field3=, >}Pkey)

but I don't want to do it this way, I have many charts and tables that I want to ignore selections in, and it will not be easy to make changes if the user wants a field added or taken off of their dashboard. How can I store this into a variable so that the variable expands to all of the fields?

I tried this:

vIgnore = Field1=, Field2=, Field3=, ........

so the expression became:

COUNT({< $(vIgnore) >}Pkey)

but qlikview doesn't seem to like this. Is there a way ignore specific fields easily? a way that I won't have to update 75 expressions everytime I need to make a change

Re: Ignoring Certain Fields using Variables in Set Analysis

swuehl — Tue, 07 Feb 2017 21:53:12 GMT

I think $(vIgnore) should work if the variable is set up correctly. How did you do it?

Or maybe have a look at:

Ignore all selections except some specific fields using Set Analysis

Re: Ignoring Certain Fields using Variables in Set Analysis

lucasdavis500 — Tue, 07 Feb 2017 21:57:43 GMT

vIgnore in the Variable Overview window is as follows:

Company=, [Product]=, [Classification]=, [FCRA]=, [Dispute Type?]=,

Here's my actual formula:

(count({<[Dummy] = {'0'}, dummy = {'1'}, Dummy = {'1'}, DateType = {'Created'}, CanonicalDate = {$(vTwelveRolling)}>} Pkey)

+

count({<Dummy = {'1'}, DateType = {'Created'}, CanonicalDate = {$(vTwelveRolling)}>} [Control Number]))

/

(count({<[Dummy] = {'0'}, dummy = {'1'}, Dummy = {'1'}, DateType = {'Created'}, CanonicalDate = {$(vTwelveRolling)}>} Pkey)

+

count({<Dummy = {'1'}, DateType = {'Created'}, CanonicalDate = {$(vTwelveRolling)}>} [Control Number]))

When I insert $(vIgnore) to the bottom half of the formula, like so:

(count({< $(vIgnore) [Dummy] = {'0'}, dummy = {'1'}, Dummy = {'1'}, DateType = {'Created'}, CanonicalDate = {$(vTwelveRolling)}>} Pkey)

+

count({<$(vIgnore) Dummy = {'1'}, DateType = {'Created'}, CanonicalDate = {$(vTwelveRolling)}>} [Control Number]))

qlikview doesn't like it

Re: Ignoring Certain Fields using Variables in Set Analysis

swuehl — Tue, 07 Feb 2017 22:01:05 GMT

What you mean with 'doesn't like it'?

Storing the entire Set Analysis of expression inside a variable

Re: Ignoring Certain Fields using Variables in Set Analysis

sunny_talwar — Tue, 07 Feb 2017 22:01:34 GMT

If you are seeing red wiggly lines, I think you need to ignore it because that is just the expression editor not understanding your syntax. Try clicking okay to see if it still works the way you would expect.

Re: Ignoring Certain Fields using Variables in Set Analysis

lucasdavis500 — Tue, 07 Feb 2017 22:03:12 GMT

No red wiggly lines, the next function Count() turns black, along with all my other fields in set analysis

Re: Ignoring Certain Fields using Variables in Set Analysis

sunny_talwar — Tue, 07 Feb 2017 22:04:20 GMT

I would still suggest clicking okay to check if you see any result or not

Re: Ignoring Certain Fields using Variables in Set Analysis

lucasdavis500 — Tue, 07 Feb 2017 22:04:48 GMT

Stefan, what is the difference between the way I have shown, and the link you provided? Why wouldn't the developer just do like I had done?

Re: Ignoring Certain Fields using Variables in Set Analysis

swuehl — Tue, 07 Feb 2017 22:09:37 GMT

If you are referring to the first link, that's a way to build the list of fields to clear dynamically (e.g. clear all fields from table Calendar, without hardcoding the field names).

If you are referring to the second link, that's just to say, don't take the syntax checker as highest judge, it's a quite imperfect tool.

Re: Ignoring Certain Fields using Variables in Set Analysis

swuehl — Tue, 07 Feb 2017 22:10:52 GMT

Duke Luke wrote:

No red wiggly lines, the next function Count() turns black, along with all my other fields in set analysis

If you use

='$(vIgnore)'

in a text box, what do you see?

Re: Ignoring Certain Fields using Variables in Set Analysis

lucasdavis500 — Tue, 07 Feb 2017 22:17:35 GMT

adding ' ' around my variable works in a text object, but not a chart. I get "Allocated Memory Exceeded" error. However, just using the variable with dollar sign expansion, i.e. $(vIgnore), in set analysis seems to be working now. The syntax-check doesn't like it, but at first glance it looks like a desired result. Before I posted this I must have had a syntax error. I was playing around with this for a while and didn't get desirable results. I will come back with a conclusion

Re: Ignoring Certain Fields using Variables in Set Analysis

swuehl — Tue, 07 Feb 2017 22:19:54 GMT

='$(vIgnore)'

is just a test for your variable expansion.

Re: Ignoring Certain Fields using Variables in Set Analysis

sasiparupudi1 — Wed, 08 Feb 2017 12:46:24 GMT

(count({< $(vIgnore) [Dummy] = {'0'}, dummy = {'1'}, Dummy = {'1'}, DateType = {'Created'}, CanonicalDate = {$(vTwelveRolling)}>} Pkey)

Change that thing to

set vIgnore='Company,Classification,FCRA,[Dispute Type?]';

(count({1<$Field-={$(vIgnore)} , [Dummy] = {'0'}, dummy = {'1'}, Dummy = {'1'}, DateType = {'Created'}, CanonicalDate = {$(vTwelveRolling)}>} Pkey)

HTH

Sasi

Re: Ignoring Certain Fields using Variables in Set Analysis

sdmech81 — Wed, 08 Feb 2017 12:56:46 GMT

Hi,

Cut down ur big expression.

Use variables and store them into variables may be like;:

var1=(count({<[Dummy] = {'0'}, dummy = {'1'}, Dummy = {'1'}, DateType = {'Created'}, CanonicalDate = {$(vTwelveRolling)}>} Pkey

Then var2,var3 and then do the summation and divisions.

Thanks