Counting Multiple Countries Per Region

souichirobenefield

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?

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

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!

 

 

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!