topic Re: Set Analysis Ignore Field in QlikView

Set Analysis Ignore Field

Thu, 23 Feb 2017 17:09:09 GMT

Hi,

I have the following table:

StudentId	Year	Class	Section	Grade
1	Current	11	A	A
2	Both	10	B	A
3	Previous	2	C	A
4	Both	3	A	B
5	Current	4	D	B
6	Both	10	D	B
7	Previous	11	D	B
8	Both	10	B	B
9	Current	11	A	B
10	Both	2	C	A

I need a count of students who belong to current/both year or Previous/Both year, ignoring the selections in Section, Class and Grade.

I am currently using the following set analysis expression, but it is giving me wrong counts. vSelectedYear is user input has 2 possible values 'Current' or 'Previous'

=if (vSelectedYear = 'Current', Count(DISTINCT{1<[Year]={'Current', 'Both'}>} [StudentId]),

Count(DISTINCT{1<[Year]={'Previous', 'Both'}>} [StudentId]))

Kindly Suggest!

Re: Set Analysis Ignore Field

petter — Thu, 23 Feb 2017 17:23:23 GMT

If you're expression is exactly like you show above it is missing an ) and have a , extra....

=if (vSelectedYear = 'Current', Count(DISTINCT{1<[Year]={'Current', 'Both'}>} [StudentId]),

Count(DISTINCT{1<[Year]={'Previous', 'Both'}>} [StudentId]) )

This is a complete expression that is error free syntactically but not necessarily logically ....

Re: Set Analysis Ignore Field

Thu, 23 Feb 2017 17:27:40 GMT

The extra ) is not extra, but closing bracket of if condition.

Re: Set Analysis Ignore Field

petter — Thu, 23 Feb 2017 17:27:57 GMT

With this simplified expression you should get the filter you want:

Count({1<[Year]={'$(vSelectedYear)', 'Both'}>} [StudentId])

Re: Set Analysis Ignore Field

Thu, 23 Feb 2017 17:31:56 GMT

Returns the same counts that my original expression returns.

Re: Set Analysis Ignore Field

sunny_talwar — Thu, 23 Feb 2017 17:37:42 GMT

What is the expected output here? Seems to be working

Current -> 8

Previous -> 7

What is the issue?

Re: Set Analysis Ignore Field

Thu, 23 Feb 2017 17:40:08 GMT

Well, on the real and bigger dataset this expression is giving wrong counts.

Re: Set Analysis Ignore Field

sunny_talwar — Thu, 23 Feb 2017 17:41:57 GMT

If its working in the sample, it would be difficult to know why it isn't working on the real application without taking a look at it. Can you introduce the same problem in your sample? May be these links can help

Preparing examples for Upload - Reduction and Data Scrambling

Uploading a Sample

Re: Set Analysis Ignore Field

petter — Thu, 23 Feb 2017 17:42:29 GMT

Yes after you edited your question .... 😉

Re: Set Analysis Ignore Field

Thu, 23 Feb 2017 17:51:20 GMT

Haha!! Only the extra comma was removed, but the extra ) was not removed from the original post.