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?

Accepted answers

LipChean_Soh

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

All comments

LipChean_Soh

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

souichirobenefield

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!

LipChean_Soh

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

dkolka

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

souichirobenefield

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?

LipChean_Soh

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

souichirobenefield

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!

Quick Links

Can you help?

Unanswered questions

How have you from-scratch built lot-level, geo-based Inventory Consumption?
We’re working on modeling inventory consumption at the lot level, accounting for shelf life considerations, since we need visibility into which lots may not consume—both for risk reporting and E&O analysis. Each item may have customers with different shelf life requirements. For example, item A may need 270 days for some…
How to Design Simulation Planning in Anaplan with Multi-Level User Input and Step-by-Step Logging?
Hi Anaplan Community, I'm working on a simulation planning model where users need the flexibility to input pricing adjustments at any level of the product hierarchy, and I’d love your guidance on best practices. 📌 Scenario Overview: We have a 4-level product hierarchy: Brand Category Sub Category SKU (lowest level) The…
ADO File Upload - Semicolon Delimiter
Hi, Based on the regional settings, my default delimiter/separator for CSV files is a Semicolon. In the classic way of uploading a csv file, I was able to select the delimiter. When using ADO, I don't have the option to select a delimiter. Uploading a Semicolon delimited file returns an error, or an upload with only one…