Set analysis query in a textbox

[#1]

Hello,

I have a text box which displays the departments without employees for two cities LN & NY.

=count({<Department=e({<Employee={*}>},City={'NY','LN'}>}Department)>}Department)

I am expecting this set analysis query to return values based on filter City = NY & LN.

However, i can see that if I select other cities, the set analysis is throwing other values. How can I modify the expression to  prevent that?

What ever selection I make, I want to make sure I get  values only for cities NY & LN.

Could anyone help me on this?

Thanks.

[#2]

Try without the E() function.

=count({<Department={<Employee={*}>},City={'NY','LN'}>}Department>}Department)

The element function E( ) you are using returns the list of excluded Departmens; those that are excluded by the selection ‘NY’,'LN' in the field City. SO you are telling qlikview to count everything in the current selection that does not have a department in NY or LN

[alexandros17]

try this:

count({<Employee=,City={'NY','LN'}>} Department)

let me know

[#4]

HI,

Dick Zeeman is correct. He's done a good job.

bcoz e()  excluded the values

[#5]

Hi,

plz share some qvw.

my mail is gkoti98@gmail.com

[lvf]

As Dick posted, but with modified syntax for the following reasons:

1. Removal of superfluous syntax

2. Best practice to put a set identifier in you set statements - using $ which is current selections

3. requirements is to show departments with no employee so lets reflect that in the Employee modifier

=count({$<Employee=IsNull(Employee),City={'NY','LN'}>}Department)

[rbecher]

Hi Sindhu,

try this:

=count({<Department=e({<City={'NY','LN'}>} Employee)>} Department)

- Ralf

[#8]

Thanks everyone

I tried all the above examples above. However, I am getting syntax error: Error in set modifier ad hoc element list.

[#9]

Tried without e as well as with $.

[rbecher]

Please check spelling of the field names (case sensitive)..