How to define relations to dates and sales figures...

Hi Bjarne Teebken,

Welcome to the SugarClub community!

First of all - there are no prizes for using less fields :) It is tempting to do everything in one field, and given enough time and patience, you will get there.... but nobody aside from yourself will be excited unfortunately - trust me - I've done that too many times!

Given that Sugar does not have the ability to define variables, it is common in complex formulae to create additional fields, purely to store interim values - making it easier to debug when something goes wrong, or to reuse a part of the calculation elsewhere.

Let's look at your business logic. Two comments:

• Any account which has not placed any orders yet (regardless of how long they have been a 'customer' for), would be a target - since if hadn't placed any orders.. how could they be a customer? 
• There should is a 4th classification of your customers - been a customer > 365 days and placed an order in < 365 days. Let's call it "Regular Customer".

Assuming the above two comments are accurate - I would do the following:

1. An integer field days_as_a_customer with a formula like abs(daysUntil($date_entered)) to work out how long a person has been a customer for. You may likely need to change date_entered with a field that accurately represents when the organisation first actually became a customer.
2. A date field most_recent_order with a formula like maxRelatedDate($opportunities,"expected_close_date") (adjust this to fit whatever module/field you're looking at order data from)
3. An integer field days_since_last_order with a formula like ifElse(isValidDate($most_recent_order ),abs(daysUntil($most_recent_order)),-1) to work out how long a person has been a customer for.
4. A status field with the following logic:
   
   

   ifElse(
       equal($most_recent_order,-1),
       "Target",
       ifElse(
           isWithinRange($days_as_a_customer,0,365),
           "New Customer",
           ifElse(
               greaterThan($days_since_last_order,365),
               "Lost","Regular Customer")))

   
   
   I've just typed that into this box here, so if there is a parenthesis out, apologies!

Now, while this addresses your request, there is a broader concept to bear in mind.

SugarLogic only recalculates when a record is edited, or you load up the record view. This means that SugarLogic which checks the "age" of something (any comparison of dates relative to today) will be out of date when you view it in a report if that record hasn't been updated in a while.

Depending upon the volume of data in your system, this may not be an issue - but if it is - to address this, you would need to either:

1. Do something which triggers a recalculation of SugarLogic on this module (approx 20 secs of human effort - go to List View, select all records, select "Recalculate Sugar Logic" - or write a script which does the same) on at least a sufficient basis to remove the likelihood that inaccuracy of data causes poor decision making. If doing this daily is fine, then it stops being a problem.
   
   
2. Rewrite the above into a scheduled job (code).

Hope this helps!

Hi Adam Frank,

this definitely helped! And im >almost< there....but not quite yet.

There is only one more hurdle to overcome: I want to combine "two logics" 

I know that you said that there aren't any prizes for using less fields, but it would be optimal in this case...

1st:  I've got the field to define for how long a company has been our customer 

---> isWithinRange($days_as_a_customer_c,0,365)

2nd: a field that sums up all sales figures that this customer has "generated" with us

---> greaterThan($all_sales_figues,0)

Both of them work perfectly...

So my plan is to combine them ike this:

ifElse(

isWithinRange($days_as_a_customer_c,0,365)

AND(

greaterThan($all_sales_figues,0),"Status 1",
"Status 2"))

No matter where i put the parenthesis, I always receive an error like: "ifElse requires exactly 3 parameters"

I guess i use wrong the functions? Or is it simply too hard to transform an idea into such a "long" function in a calculated field?

Understanding this would help me so much more in other cases aswell...

Thank you for your support!

Great to hear you are nearly there! From a purely technical POV, SugarLogic handles long functions perfectly fine - its us "mere humans" who struggle to get them right! So breaking up into multiple fields is often for our benefit more than Sugar's, as these extra fields never need to be on the layouts, or reportable - meaning nobody else sees them.

As for your follow up question - the 'and' concept works a little differently to the way you're expecting - try this instead:

ifElse(AND(isWithinRange($days_as_a_customer_c,0,365),greaterThan($all_sales_figues,0)),"Status 1","Status 2")

Hopefully this helps you get this and others over the line too

Oooooh, just like in Excel, okay!

I'll try it tomorrow (:

Btw, is there a list of all the functions and parameters that can be used in SugarCRM? I know that there is this small list in the editor itself, but are there more that are not listed right there?