topic Re: Keeping the filtering function of current selection while disregard it in count function in QlikView

Keeping the filtering function of current selection while disregard it in count function

Wed, 12 Nov 2014 08:47:00 GMT

Dear all,

I have encountered the following problem.

I have made 2 straight table in a dashboard.

Suppose the first table consists of the information for students, while the second table consists of the information of interest club in school. Since one student can belong to several interest club, I expected that when I click on one student, the second table will shows the interest group the student belong to. The problem is, there is a column displaying the total number of students in that interest club and when I click on the student, the number will become 1 as expected. I have tried to use count({} distinct student) and it can fix the problem of number of count. However, the filtering function is also disabled. That is, now the second table (the interest club) will show all the interest club instead of the interest club that the selected student belong to.

Would you please advise?

Message was edited by: ivy ting

Re: Keeping the filtering function of current selection while disregard it in count function

rubenmarin — Wed, 12 Nov 2014 08:50:30 GMT

Hi, maybe something like:

count({<student>} distinct student)

This count students removing the selection in the 'student' field

Hope this helps, if not I can give a more accurate answer if you upload a sample

Re: Keeping the filtering function of current selection while disregard it in count function

Wed, 12 Nov 2014 09:12:47 GMT

Thanks a lot. It works! Could you please explain a bit the meaning for {<student>} in the expression?

Re: Keeping the filtering function of current selection while disregard it in count function

rubenmarin — Wed, 12 Nov 2014 09:17:45 GMT

It's 'set analisys'. That means to ignore the selections in 'student' field for that count sentence

Re: Keeping the filtering function of current selection while disregard it in count function

Wed, 12 Nov 2014 09:24:01 GMT

Sorry it didn't work as expected after further testing.I can the count correct but table 2 shows all the values in the data set instead of the data of the student. It is probably because the selections in 'student' field is ignored.

Re: Keeping the filtering function of current selection while disregard it in count function

ashfaq_haseeb — Wed, 12 Nov 2014 09:25:59 GMT

Hi,

Can you post sample app with expected output to look over.

Regards

ASHFAQ

Re: Keeping the filtering function of current selection while disregard it in count function

Anonymous — Wed, 12 Nov 2014 09:44:24 GMT

Hi ,

Which fields are common in your both table (for association). Look into your model.

Can you post the sample data.

Re: Keeping the filtering function of current selection while disregard it in count function

morganaaron — Wed, 12 Nov 2014 10:08:28 GMT

Hi Ivy,

The way in which I got this to work before was to create a resident load in your script for your "interest" and load the students again, so as "students2" for example (therefore your resident load is interest and students as students2 for example).

You can then have students in a listbox, but your table becomes interest as the dimension and =count(Distinct Student2) as your expression; therefore it will filter to only the interests that student has, but will count the number of students from student2 that have that entire interest.

Re: Keeping the filtering function of current selection while disregard it in count function

rubenmarin — Wed, 12 Nov 2014 10:22:47 GMT

Hi ivy, I'll need a sample data to give a more accurate answer

Re: Keeping the filtering function of current selection while disregard it in count function

Wed, 12 Nov 2014 11:03:34 GMT

sample attached

Re: Re: Keeping the filtering function of current selection while disregard it in count function

morganaaron — Wed, 12 Nov 2014 11:10:07 GMT

See attached for what I was mentioning before. Just change the data I loaded for your existing data and alter the resident load to look at student_no and it should work!

Re: Re: Keeping the filtering function of current selection while disregard it in count function

ashfaq_haseeb — Wed, 12 Nov 2014 11:15:05 GMT

Hi,

Check if this helps

Regards

ASHFAQ

Re: Re: Keeping the filtering function of current selection while disregard it in count function

rubenmarin — Wed, 12 Nov 2014 11:17:46 GMT

Hi, check attachment if this meets your requirements.

Re: Keeping the filtering function of current selection while disregard it in count function

Anonymous — Wed, 12 Nov 2014 11:21:49 GMT

Hi,

Ruben's method should work. I if you are looking for the same.

Re: Keeping the filtering function of current selection while disregard it in count function

Wed, 12 Nov 2014 11:34:34 GMT

Thanks a lot! it works. Would you please explain a bit what is the mechanism behind the following expression? count({0"}, student_no>} DISTINCT student_no)

Re: Keeping the filtering function of current selection while disregard it in count function

rubenmarin — Wed, 12 Nov 2014 11:41:03 GMT

The filters are in set analisys:

count({<club={"=Count(student_no)>0"}, student_no>} DISTINCT student_no)

club={"=Count(student_no)>0"} filters the field 'club' to show only the clubs wich have at least one of the selected students

student_no ignores the selection in student_no, so the count will count all the students in each club