A customer has custom field that has the formula i...
# suiteanalyticsn
NElliott
02/06/2019, 1:46 PMA customer has custom field that has the formula in teh snippet below that takes 30 days off the startdate when defaulting the value.
They now want take off one month instead of 30 days.
I've found an SQL example of taking off one month and I'm guessing this can be used but frankly no idea how to format this to meet the requirement.
Can anyone advise please?
r
ryanhowson
02/06/2019, 2:02 PMNS has an ADD_MONTHS function - I haven't used it myself but sounds like that could work?
ryanhowson
02/06/2019, 2:04 PMADD_MONTHS({today},-1) returns 6th Jan for me
n
NElliott
02/06/2019, 2:40 PMThinking:
CASE WHEN {custbody_order_type.id}='2' then (ADD_MONTHS({startdate},-1)) else {expectedclosedate} end
NElliott
02/06/2019, 2:41 PMI am going to test that theory a little later today.
@ryanhowson thanks for replying!
NElliott
02/06/2019, 4:57 PM@ryanhowson That kinda works except with 28th Feb it throws it back to 31st Jan everything else I've tried seems OK 🤨
r
ryanhowson
02/06/2019, 4:58 PMtechnically 1 month before 28th Feb is 31st January
n
NElliott
02/06/2019, 4:59 PMOnly if you ascribe a set number of days to a "month" which is what I was hoping to avoid
NElliott
02/06/2019, 5:02 PM30th Dec goes back to 30th November,
31st Dec goes back to 30th November, both as expected. 28 Feb I'd expect to back to 28th Jan, no? Seems inconsistant?
r
ryanhowson
02/06/2019, 5:09 PMTrue, just tried in BigQuery and subtracting a month there from 28th Feb there returns 28th Jan
n
NElliott
02/06/2019, 5:17 PM@ryanhowson yeah exactly weirder than weird