Announcement

Collapse
No announcement yet.

How to use the GREATEST function in a complex expression with a date field/

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • How to use the GREATEST function in a complex expression with a date field/

    Hello. New EspoCRM-user here. I do have some SQL knowledge.

    A little background: we store donations in our system. People can donate 'oneoff', or frequently (monthly, annually)

    I was asked to make a report where i list people that, in the previous month, has stopped their frequent donation.

    So i am planning to establish the last donation of a person that is listed as a 'frequent' donor, and test whether that last donation is more than x days in the past.

    I am looking at 'complex expressions' ( https://docs.espocrm.com/user-guide/...x-expressions/ ), and found a GREATEST function. So i thought i could do:

    GREATEST(donatiedatum)<'2024-01-01'

    (the field tested translates to 'donation date')
    (on https://docs.espocrm.com/administrat...mula/datetime/ i found the date format should be like '2024-01-01')

    But whether i use the above, or the inverse (GREATEST(donatiedatum)>'2024-01-01'): i do not get any results.

    I was unable to find any/relevant examples of the use of this function. Anybody any tips on how to use this function / what i'm doing wrong?

  • #2
    Hi,
    i think https://docs.espocrm.com/user-guide/reports/#columns
    MAX(donatiedatum) will do what you search.

    And you can "Filter" after some "date" field

    Comment


    • #3
      As item Mentioned you don't need a function, you can just add the donation date field in the filter section and there is a function in it old than x Days. see attached screenshot
      Attached Files

      Comment


      • #4
        Thank you both, will try as soon as i find time, and report back if not successful. Thanks again!

        Comment

        Working...
        X