topic Re: Set analysis query in a textbox in QlikView

Set analysis query in a textbox

Tue, 15 Jul 2014 13:48:28 GMT

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.

Re: Set analysis query in a textbox

Tue, 15 Jul 2014 14:06:26 GMT

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

Re: Set analysis query in a textbox

alexandros17 — Tue, 15 Jul 2014 14:07:40 GMT

try this:

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

let me know

Re: Set analysis query in a textbox

Tue, 15 Jul 2014 14:19:18 GMT

HI,

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

bcoz e() excluded the values

Re: Set analysis query in a textbox

Tue, 15 Jul 2014 14:20:40 GMT

Hi,

plz share some qvw.

my mail is gkoti98@gmail.com

Re: Set analysis query in a textbox

lvf — Tue, 15 Jul 2014 15:16:07 GMT

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)

Re: Set analysis query in a textbox

rbecher — Wed, 16 Jul 2014 06:47:33 GMT

Hi Sindhu,

try this:

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

- Ralf

Re: Set analysis query in a textbox

Wed, 16 Jul 2014 07:50:55 GMT

Thanks everyone

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

Re: Set analysis query in a textbox

Wed, 16 Jul 2014 07:51:33 GMT

Tried without e as well as with $.

Re: Set analysis query in a textbox

rbecher — Wed, 16 Jul 2014 08:04:40 GMT

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