Excluding an item from the list box and performing a function

[rt_new_user]

Hi all,

can anyone please help with this problem as i am very new to QV and still learning my way around to use different expressions to get things done:

Need to Sum  of No of customers from the list box 'No of customers' for each country excluding Brazil.

[MayilVahanan]

Hi

Try like this

Dimension:

Country

Expression

=Sum({<Country -= {'Brazil'}>}[No of Customers])

[senpradip007]

Try like

Sum( if(Country<>'Brazil', Customer)

[jagan]

Hi Ravi,

Use this set analysis expression

Expression

=Sum({<Country -= {'Brazil'}>}[No of Customers])

-= operator excludes the given values from Current possible values.

Hope this helps you.

Regards,

Jagan.

[rt_new_user]

I should have been more elaborative from the start:

Basically i have a field “Operator” which has data in the format for example (Countryname – Opeartor name)

I have used the subfield function to break it into two additional list boxes as below:

I have then used Dimension as a Group with both (“Country” and “Operator name”) for drill down purpose. As i want my chart to show firstly by Country and then by Operator. Aim is to exclude one Operator completely from the total, for example AUSTRALIA – Telstra (505 01) from the Operator list box (please note Operator list box has also been broken down to Country and Operator name

[sindu_bharathi]

Hi,

use If(country<>'Brazil',[No of Customers])

Regards,

Sindu

[jagan]

Hi Ravi,

In script create a new field excluding the operator AUSTRALIA – Telstra (505 01), try script like below

LOAD

*,

SubField(NewField, '-', 1) AS NewCountry,

SubField(NewField, '-',  2) AS NewOperatorName;

LOAD

*,

If(Operator <>  'AUSTRALIA – Telstra (505 01)', Operator) AS NewField

FROM Data;

Hope this helps you.

Regards,

Jagan.

[rt_new_user]

Hi Jagan

Tried this but didn’t work. Got a”file not founf” error ☹

Directory;

LOAD Date,

Time,

Operator,

if(Operator<>'AUSTRALIA - Telstra (505 01)',Operator)as OperatorminusTelstra,

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

SubField(OperatorminusTelstra, '-' ,2) as "Operator name",

FROM

Inbound_140917_0903.xlsx

(ooxml, embedded labels, table is Inbound_140917_0903);

[jagan]

Hi,

Try this script

Directory;

LOAD

*,

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

     SubField(OperatorminusTelstra, '-' ,2) as "Operator name";

LOAD Date,

     Time,

     Operator,

if(Operator<>'AUSTRALIA - Telstra (505 01)',Operator)as OperatorminusTelstra

FROM

Inbound_140917_0903.xlsx

(ooxml, embedded labels, table is Inbound_140917_0903);

Regards,

Jagan.

[rt_new_user]

Sort of worked partially as i don’t see the AUSTRALIA-Telstra (505 01) in Operator list box, however the list box ‘ No of customers’ has disappeared. I don’t see it in the list of available fields ☹

Can you also explain what your script does please?

Regards