topic Re: How to exclude an item from a list box when performing aggregation finction? in QlikView

How to exclude an item from a list box when performing aggregation finction?

rt_new_user — Tue, 23 Sep 2014 03:05:37 GMT

Hi All

i asked this question before too but it looked liked it worked but didn't really. Didn't know how to re-open that discussion hence posting a new one.

I have a list box which i used subfield function to generate two other list boxes from using the below command:

Directory;

LOAD Date,

Time,

Operator,

SubField(Operator,'-',1) as Country,

subfield(Operator,'-',2) as Operatorname,

[No of IMSI's] as 'No of items'

FROM

Now that i have two desired list boxes one as 'Country' and other as 'Operatorname' i want to table to show Sum of 'No of items' for all the countries but do not show the Sum when the country name is Australia. I used the below process but still see Sum for Australia??

Re: How to exclude an item from a list box when performing aggregation finction?

JonnyPoole — Tue, 23 Sep 2014 03:13:11 GMT

missing some {} around the SET MODIFIER :

sum( {<Country -= {AUSTRALIA}>} [No of items])

Re: How to exclude an item from a list box when performing aggregation finction?

aveeeeeee7en — Tue, 23 Sep 2014 04:17:44 GMT

You have not used Curly Brackets which is the standard syntax of Set Analysis to pass specific values for fields and calculate result on its basis.

Use this: {'AUSTRALIA'}

Try this:

sum( {<Country = {'*'}-{'AUSTRALIA'}>} [No of items])

or,

sum( {<Country -= {'AUSTRALIA'}>} [No of items])

Regards

Av7eN

Re: How to exclude an item from a list box when performing aggregation finction?

rt_new_user — Tue, 23 Sep 2014 04:47:18 GMT

Tried the below set analysis expression but same result..didn’t work ☹

Re: How to exclude an item from a list box when performing aggregation finction?

rt_new_user — Tue, 23 Sep 2014 04:47:54 GMT

Tried both expressions but still see AUSTRALIA in the table ☹

Re: How to exclude an item from a list box when performing aggregation finction?

jagan — Tue, 23 Sep 2014 05:10:17 GMT

Hi Ravi,

I think the field has a space at the end, trim those space like below

Directory;

LOAD Date,

Time,

Operator,

Trim(SubField(Operator,'-',1)) as Country,

Trim(subfield(Operator,'-',2)) as Operatorname,

[No of IMSI's] as 'No of items'

FROM

and then try below expression

=Sum( {<Country -= {'AUSTRALIA'}>} [No of items])

Hope this helps you.

Regards,

Jagan.

Re: How to exclude an item from a list box when performing aggregation finction?

rt_new_user — Tue, 23 Sep 2014 05:27:13 GMT

Great!!

I learnt two things from you in the last two days!!

What are these Trim functions for? What made you think that this data set requires a TRIM command? Eager to know please.

Thanks

Re: How to exclude an item from a list box when performing aggregation finction?

jagan — Tue, 23 Sep 2014 05:41:50 GMT

Hi,

I noticed that your values has a separator - with spaces on both sides, but in script you specified only - in SubField(), instead you should give

subfield(Operator,' - ',2) - giving space on both sides of hyphen

Trim(subfield(Operator,'-',2)) - trimming the extra spaces

Hope this helps you.