I am really stuck, have spent too much time spinning my wheels on this. I have created a new report with values coming from Job cost, Contracts, Billings etc all coming from transactions using Accounting Dates. I am using a Prompt so I have a "Cut-off" date. This all works great until I attempt to bring in the LM and MTD columns using NEWDATE.
Example:
Cut-off Date 1/31/18 (Prompt called ENDDATE, must be last day of Accounting Month)
My report returns correct values for cost, contract value, billings through 1/31/18.
I created four dates in Design Formulas to capture LM and MTD
Jobcost is a Public Formula
MTDBEG 1st day of month=NEWDATE(ENDDATE,-1,1,0) This represents 1/1/18
MTDEND Last day of month=NEWDATE(ENDDATE,0,0,0) This represents 1/31/18
LMBEG 1st day of month=NEWDATE(ENDDATE,-2,1,0) This represents 12/1/17
LMEND Last day of month =NEWDATE(ENDDATE,-1,0,0) This represents 12/31/17
JCcost= All cost types
My formulas to capture the Job costs are:
LM
ASUM(Job,IF (Accounting Date >= LMBEG AND Accounting Date <= LMEND, JCcost))
MTD
ASUM(Job,IF (Accounting Date >= MTDBEG AND Accounting Date <= LMEND, JCcost))
My error is "An accumulating function cannot reference a formula that performs an accumulation. An accumulation is performed by the formula: Job Cost".
Anyone have any ideas on how to get around my error above?
I need to capture JobCost only so I can't use Amount field, unless there is a way to condition that field for that column only.
Any assistance would be greatly appreciated.