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.

 

CustomerCountryRegion Inter-Region?
Customer AUnited StatesAM No
Customer AUnited KingdomEU Yes
Customer AItalyEU Yes
Customer ASingaporeAP No
Customer BSingaporeAP No
Customer BFranceEU No
Customer BUnited StatesAM No
Customer CGermanyEU Yes
Customer CFranceEU Yes

Any advice on how to build this module?

Best Answer

  • 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

Answers

  • 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

  • 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?

     

    Sample.PNG

     

    Thank you so much!

     

     

  • 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

  • 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?

  • 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

  • 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!