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?

1 ACCEPTED SOLUTION

Accepted Solutions
Certified Master Anaplanner

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

7 REPLIES 7
Certified Master Anaplanner

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

New Contributor

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!

Certified Master Anaplanner

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

Contributor

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

New Contributor

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?

Certified Master Anaplanner

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

New Contributor

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!