Apex Code Development You need to sign in to do that
Don't have an account?
Something went wrong while executing the End Date node: invalid field expression
I am trying to calculate the end date of an opportunity line item.
Here is the formula I am using:
Data TypeFormula
DATE(YEAR(ServiceDate)+FLOOR((MONTH(ServiceDate +Term__c))/12)+if(MOD(MONTH(ServiceDate+Term__c),12)=0,-1,0), if(MOD(MONTH(ServiceDate+Term__c),12)=0,12,MOD(MONTH(ServiceDate+Term__c),12)), DAY(ServiceDate))-1
and when I run the Dataflow, this is the error I am getting:
Something went wrong while executing the End Date node: invalid field expression DATE(YEAR(ServiceDate)+FLOOR((MONTH(ServiceDate +Term__c))/12)+if(MOD(MONTH(ServiceDate+Term__c),12)=0,-1,0), if(MOD(MONTH(ServiceDate+Term__c),12)=0,12,MOD(MONTH(ServiceDate+Term__c),12)), DAY(ServiceDate))-1;; for field 'EndDate': Syntax Error at position 102 after token = :: ;+Term__c),12)⇾=⇽0,-1,0), if(MO; (02K380000008giyEAA_03C38000000D6cqEAC)
I believe the syntax is correct, so any help would be greatly appreciated.
Thanks!
Tasia
Here is the formula I am using:
Data TypeFormula
DATE(YEAR(ServiceDate)+FLOOR((MONTH(ServiceDate +Term__c))/12)+if(MOD(MONTH(ServiceDate+Term__c),12)=0,-1,0), if(MOD(MONTH(ServiceDate+Term__c),12)=0,12,MOD(MONTH(ServiceDate+Term__c),12)), DAY(ServiceDate))-1
and when I run the Dataflow, this is the error I am getting:
Something went wrong while executing the End Date node: invalid field expression DATE(YEAR(ServiceDate)+FLOOR((MONTH(ServiceDate +Term__c))/12)+if(MOD(MONTH(ServiceDate+Term__c),12)=0,-1,0), if(MOD(MONTH(ServiceDate+Term__c),12)=0,12,MOD(MONTH(ServiceDate+Term__c),12)), DAY(ServiceDate))-1;; for field 'EndDate': Syntax Error at position 102 after token = :: ;+Term__c),12)⇾=⇽0,-1,0), if(MO; (02K380000008giyEAA_03C38000000D6cqEAC)
I believe the syntax is correct, so any help would be greatly appreciated.
Thanks!
Tasia
Could you show a snapshot of your open node with this used formula field (input, output, expression)?
I have a limited access to Dataflows (Wave Analytics) and I have a doubt about your used method to include this formula.
I may not be able to help you but we will have tried.
Here is the JSON code:
"End Date": { "action": "computeExpression",
"parameters": {
"mergeWithSource": true,
"computedFields": [
{
"name": "EndDate",
"label": "EndDate",
"type": "Date",
"saqlExpression": "DATE(YEAR(ServiceDate)+FLOOR((MONTH(ServiceDate +Term__c))/12)+if(MOD(MONTH(ServiceDate+Term__c),12)=0,-1,0), if(MOD(MONTH(ServiceDate+Term__c),12)=0,12,MOD(MONTH(ServiceDate+Term__c),12)), DAY(ServiceDate))-1\n",
"format": "mm/dd/yyyy"
}
],
"source": "sfdcDigest_OpportunityLineItem" } }
Above is a picture of the dataflow. The End Date component is not attached to the register because it was erroring out. I have highlighted in red.
Thank you for looking at this.
Tasia
That is what I feared : you have used a standard known Salesforce formula for a SAQL expression and that could not be correct (perhaps).
SAQL is completely different. It is a language close to Pig Latin.
It seems that there is no control at all by default during the save (I have done the same action as you yesterday in a node and I could save it without any warning but it is not a SAQL expression at all).
Documentation: SAQL is influenced by the Pig Latin programming language, but their implementations differ and they aren’t compatible.
https://developer.salesforce.com/docs/atlas.en-us.198.0.bi_dev_guide_saql.meta/bi_dev_guide_saql/bi_saql_statements.htm
https://developer.salesforce.com/docs/atlas.en-us.198.0.bi_dev_guide_saql.meta/bi_dev_guide_saql/bi_saql_functions_date.htm
The principle but also completely different: Apache Pig is a platform for analyzing large data sets that consists of a high-level language for expressing data analysis programs, coupled with infrastructure for evaluating these programs. The salient property of Pig programs is that their structure is amenable to substantial parallelization, which in turns enables them to handle very large data sets.
https://pig.apache.org/
I will try to help you tonight but we need a SAQL expert ideally.
https://help.salesforce.com/articleView?id=bi_integrate_saql_transformation.htm&type=5
The big problem is that we will find very few samples on the internet. SAQL is very special.
You should use all the possible forums (stackoverflow, success community) to augment your chances of having a useful answer.
That does make sense. I was so confused as to why the formula was not working - but the SAQL explanation clears things up.
I will do some further investigation as well into SAQL. I appreciate your help and welcome any further assistance you can provide.
Thank you also for these links, I will begin my research there.
Tasia
I didn't use the modules (trailhead) for the dataflows in WaveAnalytics until now (it is a blind spot for me).
But I will try to convert the formula. You will probably find the solution for this problem before me.
DATE(
YEAR(ServiceDate)+FLOOR((MONTH(ServiceDate +Term__c))/12)+if(MOD(MONTH(ServiceDate+Term__c),12)=0,-1,0), // YEAR
if(MOD(MONTH(ServiceDate+Term__c),12)=0,12,MOD(MONTH(ServiceDate+Term__c),12)), // MONTH
DAY(ServiceDate) // DAY
) - 1
Do you know what is the goal of this formula? (it is a rather bizarre formula for me but it should have its own logic surely)
and what is the range for the variable Term__c (logically a number of day, 1-31 ?).
Sorry for the delayed response - I was on vacation the past couple days.
The goal behind the formula is to calculate when the contract will end - when it will no longer be a valid contract. To do this, I calculate the start date plus the term (which is the number of months the contract lasts). However, the formula is much more complex to account for if the end date is in the next year. So, if the term plus the start date goes beyond December, then the formula starts over again in January.
Does that help?
I agree that I cannot find much information on SAQL formula writing for this kind of formula. I will keep you posted if I discover more.
Thanks,
Tasia
That could help because there are other changes to do probably.
MONTH ( ServiceDate +Term__c ) ) /12 : we can only add days to dates directly.
If Term__c is a number of months, you have to convert it in days (x 31) or put it outside the parentheses.
That is not the most important and you would have solve these problems during the tests but here nothing is testable in the flow.
The idea is to have a right "standard" formula at first with an explanation in plain english and then you can ask for the equivalent in SAQL expression.
If the formula is already wrong in the standard format, the translation will be also wrong but we can rectify the all thing according your explanations in plain english right now (perhaps impossible, perhaps very easy in SAQL, I don't know).
That could be interesting to have the feedback from other developer forums for Salesforce (even if the questions about SAQL are very rare).
For the standard formulas, there are "champions" on the "success community" for instance but as soon as it is SAQL, there are far fewer experts.
The situation is serious, though not hopeless.
Alain
Yes it is a tricky situation for sure. I have reached out to my Salesforce Account Rep to see if he has any resources that are more versed in SAQL. Hopefully, he has some ideas, as I am a bit stuck.
I do see what you mean about adding the term outside the parentheses or converting it to days. That is a good place to start.
Thanks again for your help!
Tasia
Even the Salesforce support (cases) could be limited for SAQL (the experts are not at the "hot line" services perforce; working in projects is more remunerative but I hope they will help you).
Self-help using forums could be quicker and you did well to try an open question here but many people will suggest a solution like you with a "standard" formula (all the people will try that at first because there is no warning during the save).