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

[rt_new_user]

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??

[JonnyPoole]

missing some {} around the SET MODIFIER :

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

[aveeeeeee7en]

Hi

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

[rt_new_user]

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

[rt_new_user]

Tried both expressions but still see AUSTRALIA in the table ☹

[jagan · Accepted solution]

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.

[rt_new_user]

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

[jagan]

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

or

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

Hope this helps you.