New Contributor

## Counting Multiple Countries Per Region

I have a module that needs to tell me whether a customer is present in more than one country in each region. For example, Customer A is only present in United States under AM region. This means that it is not an inter-region customer in AM. But in EU region, it is present in United Kingdom and Italy, so it is an Inter-Region customer in EU. Please see table below for visual reference. I can't figure out a formula for the Inter-Region column to get this requirement.

 Customer Country Region Inter-Region? Customer A United States AM No Customer A United Kingdom EU Yes Customer A Italy EU Yes Customer A Singapore AP No Customer B Singapore AP No Customer B France EU No Customer B United States AM No Customer C Germany EU Yes Customer C France EU Yes

Any advice on how to build this module?

7 REPLIES 7
Highlighted
Master Anaplanner/Community Boss

## Re: Counting Multiple Countries Per Region

Hi,

Assuming you have 3 lists below, and Region is the parent of Country via the Parent Hierarchy relationship.

1. Region

2. Country

3. Customer

Following DISCO methodology, you create a S(ystem) module called 'Country Properties' with the following line item:

1. Parent = PARENT(ITEM(Country))

Assuming you have a module 'Customer-Country Assignment' with 'Customer' and 'Country' as lists, and with the line items below:

1. Active? which is an input field, with format = Boolean

2. Count = IF Active? then 1 else 0, Format = Number, Summary = Sum

Create a module 'Customer Spread' with 'Customer' as list, and with the line item below:

1. Inter-Region? = Count[LOOKUP:Country Properties.Parent] >1

Thanks,

LipChean

Highlighted
New Contributor

## Re: Counting Multiple Countries Per Region

Thank you for this. But, the Customer assignment is done in a list with Countries as parents. So the module would actually look the attached file. Any workaround for this?

Thank you so much!

Highlighted
Master Anaplanner/Community Boss

## Re: Counting Multiple Countries Per Region

Hi,

'Customer', 'Country' and 'Region' have the following relationship:

1. Customer is a child of Country,

2. Country is a child of Region

And Customer is seen repeating under different 'Country', so 'Customer' is a numbered list. I'm assuming you have another list, i.e. 'Real Customer'.

I'm assuming 'Real Customer' is a property of 'Customer'.

Create a module called 'Customer Properties' with list 'Customer' and line items below:

1. Region = Parent(Parent(Item('Customer'))), format = Region

2. Real Customer = Customer.Real Customer, format = Real Customer

3. Count = 1

Create a module called 'Real Customer-Region Assignment' with 'Real Customer' and 'Region' lists and line items below:

1. Count = Customer Properties.Count[Sum:Customer Properties.Region, Sum:Customer Properties.Real Customer]

Create a module called 'Real Customer Spread' with 'Real Customer' list and line item below:

1. Inter-Region? = 'Real Customer-Region Assignment.Count' > 1, format = Boolean

Thanks,

LipChean

Highlighted
Contributor

## Re: Counting Multiple Countries Per Region

Hi,

To clarify consider moving the assignment of customer > country out of a List and into a Module. Having customers roll up under multiple parents within the same list is not a true hierarchical relationship and may cause confusion and inefficiencies in the future.  Having the assignment in a module will allow you to capture and track the ever evolving relationship between customer and country.

Regards,

Dave

Highlighted
New Contributor

## Re: Counting Multiple Countries Per Region

I created Country Properties module that Applies to Country list, FORMAT: Country. So I was able to get get the Regions per Country in this module.

For the Customer Spread module, I can't seem to make it work. How do I set up the Count line item in this module?

Highlighted
Master Anaplanner/Community Boss

## Re: Counting Multiple Countries Per Region

Hi,

I'm curious why there's a 'Country Properties' module?

In the solution i proposed, there's no 'Country Properties'. Perhaps you can share some screens, and we can take it from there.

Thanks,

LipChean

Highlighted
New Contributor

## Re: Counting Multiple Countries Per Region

Hi,

I was referring to your first solution. I ended up using the second solution anyway so I had everything setup and working already. Thank you so much!