Matrix of Conditions

Highlighted
Community Boss

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!

4 REPLIES 4
Highlighted
Community Boss

Re: Matrix of Conditions

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

 

Highlighted
Master Anaplanner/Community Boss

Re: Matrix of Conditions

@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

Highlighted
Community Boss

Re: Matrix of Conditions

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

 

 

Highlighted
Community Boss

Re: Matrix of Conditions

Love it Arun!

 

Thank you very much

 

Mark