Matrix of Conditions

Hi Team,

 

I'm trying create some logic whereby I classify each Customer Account as either Existing, New Business or Churned for each Financial Year based on whether they had revenue in the last month of the previous financial year compared with the current year

 

I have created the Matrix below (the columns are Line Items) for the rules of classification

 

clipboard_image_0.png

 

Below is the Module I created where I can classify each customer as 1 of the above based on the revenue but I cannot determine what the formula would be? I did not want to create a large IF statement (as in IF Opening ARR = TRUE & Closing ARR = TRUE THEN Existing). FYI I will be referencing a Module with Customers as rows & Time as columns & I will use the Mapping below to return the correct months revenue value

 

Is there a method to determine whether the customer has satisfied both criteria and return the List item?

 

clipboard_image_2.png

clipboard_image_1.png

 

Thanks!

Answers

  • Yes @MarkTurkenburg , it is possible.

     

    I give you quick example, this will give you an idea.

     

    Lets say i have customer tiers as follows,

    clipboard_image_0.png

     

    I want to classify my customers based on the above tiers, if the revenue is above the min and below the max, then it should be classified in the respective tier. To avoid an 'IF' for each tier, i would rather cross the tiers with customer (note  the applies to for tier class) and do the comparison, return the Item(Customer tier). Set the summary to first non-blank, hence you get a unique tier for each customer.

    clipboard_image_1.png

     

    clipboard_image_2.png

     

    What is happening inside the Tier class is,

    clipboard_image_3.png

     

    By this way, you dont have to maintain a long IF. Hope this helps.

     

    Thanks

    Arubn

     

  • @MarkTurkenburg 

     

    Top Tip, just as an aside, you wouldn't/shouldn't write a formula such as:

     

    IF Opening ARR = TRUE AND Closing ARR = TRUE THEN ...

     

    when 

     

    IF Opening ARR AND Closing ARR THEN.... will do

     

    There is no need to check for the TRUE

     

    Saves typing and keeps the formulas shorter!

    David

  • Love it Arun!

     

    Thank you very much

     

    Mark

  • Cheers David - always looking for faster & smarter ways to model!