topic Re: exclude function for dimension in QlikView

exclude function for dimension

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

Hi all

I have a fields in straight table and i want to exclude some values in dimension dynamically.

fields like

A	B	C	count(A)
apple	red	3	1
ball	green	4	1
cat	yellow	3	1
dog	white	2	1
elephant	ash	5	1
fox	orange	1	1

now if i enter any value in input box it should not visible in the straight table

example :

if i enter apple,ball

it should not appear in the straight table

Re: exclude function for dimension

Frank_Hartmann — Tue, 02 Oct 2018 11:35:45 GMT

Use this as expression:

count({1-$}A)

Now create a listbox and select the values which you want to exclude from straight table

Re: exclude function for dimension

manoj217 — Tue, 02 Oct 2018 12:38:33 GMT

thanks for your reply

my requirement is different..

user will enter some values in input box now i dont want to show those values in straight table.

then automatically count will be adjusted right

Re: exclude function for dimension

manoj217 — Tue, 02 Oct 2018 12:40:53 GMT

i want to write exclude condition to the dimension values in a straight table and those values also change in dynamically

user will give input those values through input box

input values shouldn't appear in the straight table

Re: exclude function for dimension

jensmunnichs — Tue, 02 Oct 2018 13:09:36 GMT

Is this what you're looking for?

Re: exclude function for dimension

manoj217 — Tue, 02 Oct 2018 13:16:32 GMT

i have used =if( not Match (A ,'apple'), A) this function but it is not satisfying my requirement

Re: exclude function for dimension

manoj217 — Tue, 02 Oct 2018 13:18:51 GMT

user want to remove some values

if he removes a value in field A then entire horizontal values need to remove in straight table

Re: exclude function for dimension

jensmunnichs — Tue, 02 Oct 2018 13:29:51 GMT

Does my file not do that? When you type something in the input box for any of the dimensions it removes that entire row from the straight table on the right. I saved it with the input box already filled in, if you remove the input from the input box you'll see the rows re-appear in the straight table.

Or did you want them to be gone permanently?

Re: exclude function for dimension

manoj217 — Tue, 02 Oct 2018 13:35:17 GMT

no for temporarily only

i want to find the count of values in a field but sometimes user wants the count of values without his selection

and he wants to hide all the data in the straight table

Re: exclude function for dimension

manoj217 — Tue, 02 Oct 2018 13:42:51 GMT

count({1-$}A)

this expression is working as expected but initially i want to show all data but it is showing count =0

Re: exclude function for dimension

jensmunnichs — Tue, 02 Oct 2018 13:42:56 GMT

So like this? I'm sorry if I'm not getting this right.

If you want to exclude based on the selections made in a listbox instead of an input box you'd have to use franky_h79's original suggestion.

Re: exclude function for dimension

jensmunnichs — Tue, 02 Oct 2018 13:48:31 GMT

So if you do want to exclude based on listboxes instead of an input box, check the top table of the 2 in the attached file.

Re: exclude function for dimension

manoj217 — Tue, 02 Oct 2018 13:48:36 GMT

not able to create a list box user is not accepting.

he wants to give the input

Re: exclude function for dimension

manoj217 — Tue, 02 Oct 2018 13:50:16 GMT

your output is correct my requirement is also same

input values is also not in a single field he will give A and B both fields

Re: exclude function for dimension

manoj217 — Tue, 02 Oct 2018 13:53:23 GMT

already list box is available but if u made any selections on this may impact on other objects so he dont want to disturb the other objects he want to exclude those values in straight table only

Re: exclude function for dimension

Frank_Hartmann — Tue, 02 Oct 2018 13:54:07 GMT

then use this as expression:

if(len(GetFieldSelections(A))<>0,count({1-$}A),count({$}A))

Re: exclude function for dimension

jensmunnichs — Tue, 02 Oct 2018 14:12:29 GMT

If you enter something in 'ExcludeB' or ExcludeC' it will exclude those values as well, see below. If you only want values from fields A and B to be excluded you can remove the 'ExcludeC' variable from the input box in its properties, you'll also have to remove ', C-={$(ExcludeC)' from the expression in the straight table properties.

Re: exclude function for dimension

manoj217 — Tue, 02 Oct 2018 14:17:12 GMT

if i doesn't enter any values then it is showing all values?

can you pls share the variable expression

Re: exclude function for dimension

manoj217 — Tue, 02 Oct 2018 14:18:42 GMT

in your expression have $ instead of that i want a variable that variable value is entered by input box not with the list box selections

Re: exclude function for dimension

jensmunnichs — Tue, 02 Oct 2018 14:35:20 GMT

Yes, if you leave the input box empty it will show all values.

The expression used in the straight table is as follows:

Count({<A-={$(ExcludeA)}, B-={$(ExcludeB)}, C-={$(ExcludeC)}>} A)

The variables don't have an expression, they're set by the input box. I've attached the file that has this solution in it, have a look.

Keep in mind that Listboxes elsewhere in the document will still affect this straight table. I'm pretty sure you can solve this by simply adding a 1 to the set analysis expression:

Count({1<A-={$(ExcludeA)}, B-={$(ExcludeB)}, C-={$(ExcludeC)}>} A)

Hope this helps