Inlist operator in Webi variable

[#1]

Hi

I am stuck with one syntax.

I have a dimension called country with several countries in it such as US Canada and then several middle eastern countries. I want US as separate country, Canada as separate country and all the middle eastern countries grouped as "International"  together, plz see the screenshot below for the current structure

I used a inlist operator like this --> =If([Country] InList("Qatar";"UAE.";"Kuwait";"Oman";"Saudi Arabia"); "International")

but dont know how to use this operator to keep US and Canada still as a separate group within country dimension, I want the report to look like this

"International" will have all the middle eastern countries grouped together and the sales will be cumulative accordingly . (I can do the formatting to adjust the look and feel of the report later)

using above inlist syntax, I can have either Canada or US separate with international but cant have both Canada and US along with International

for example by using this --> If([Country] InList("Qatar";"UAE.";"Kuwait";"Oman";"Saudi Arabia"); "International"; "Canada")

Or I ca use US instead of Canada in the syntax.

I don't know how to use both US and Canada as an output for my requirement.

Can someone please help in the syntax, will appreciate, thanks alot in advance and will assign points.

Regards

[jyothirmayee_s · Accepted solution]

Hi,

Try this:

=If([Country] InList("Qatar";"UAE.";"Kuwait";"Oman";"Saudi Arabia"); "International";[Country])

Thanks,

Jothi

[former_member203111 · Accepted solution]

Hi Jothi,

Thanks for your reply. Your syntax is correct but now the order in which the country group appears are wrong.

It is suppose to be

USA

Canada

International

But I am getting

Canada

International

USA

Is there a way to fix this, thanks

[jyothirmayee_s · Accepted solution]

Hi,

Use the sort function to fix it . You can manage the sorts with Custom order.

Thanks,

Jothi