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
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?
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,
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.
What is happening inside the Tier class is,
By this way, you dont have to maintain a long IF. Hope this helps.
Thanks
Arubn
1 -
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
0 -
Love it Arun!
Thank you very much
Mark
0 -
Cheers David - always looking for faster & smarter ways to model!
0
