Skip to main content

Date calculations on full months

  • November 16, 2018
  • 1 reply
  • 22 views
H
Forum|alt.badge.img+10

Hey,

for our little CRM I’m trying to builds something based on customer revenue based on time. Like sales generated within the last 12 months.

I don’t want to calculate that on a daily basis but only check once a month on the last 12 months. I do have all the basic calcs setup but I’m unsure about the way on how to only look at full months. I thought about calculating back from today to the last day of the last month but didn’t find a way yet.

Any idea?

What I currently do is

IF(DATETIME_DIFF(Orderdate, NOW(),'d') > -365, Ordervalue, 0)

in the order table and then roll it up in the customer table.

Appreciate any idea on that.

    This topic has been closed for replies.

    1 reply

    J
    Forum|alt.badge.img+18

    So you are currently rolling up a sliding 365-day window.
    What you want is to roll up a sliding 12-month window that stops on the first day of the 12th month back? Is that right? So, for example, if TODAY() is 12/15/2018, you want to Rollup revenue back to 1/1/2018, as opposed to rolling up revenue back to 12/15/2017?

      Terms & ConditionsAccessibility statement