Report Designer - NEWDATE to generate LM(Last Month) and MTD(Month to Date)

Question

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.

Art Minds · Accepted Answer

The error message is indicating that your formula "JCcost" includes the ASUM function and it's prohibited to use a formula with ASUM inside another ASUM. You must recreated the "JCcost" formula inside each of the ASUM date range specific formulas. You could use the JC system formula for JC Cost Type transaction inside each of your formulas in an "IF" statement to narrow the date range. 
 For example, this formula will return the accumulated Cost for the job, without regard to date ranges: 
 ASUM(Job {JC Transaction}, IF(JC Cost Tran Condition {JC Formula}, Amount {JC Transaction}, 0)) 
 Please note that if you're not familiar with the annotation of fields and files, the portion inside the braces { } is the file name or source of the field referenced. If you've never used the JC Formulas, you will find them in your file list when you click Index. Here's an example: 
 
 You'll find the formula "JC Cost Tran Condition" there which tests if the "transaction type" of each JC transaction is one of five cost transaction types. 
 I would then restructure your date range formulas to merely return a true/false response for each specific date range, for example: 
 MTD Date Range formula: Accounting Date >= MTDBEG and Accounting Date <= MTDEND 
 Then you just need to copy the above formula I provided multiple times and nest an additional IF condition for each of your specific date ranges, as shown here: 
 MTD Amount formula: ASUM(Job (JC Transaction), IF (MTD (Design formula), IF(JC Cost Tran Condition (JC Formula), Amount (JC Transaction), 0))) 
 Building your formulas in a modular fashion rather than trying to incorporate too many factors in a single formula makes design and testing much easier.

Sage Construction & Real Estate

Report Designer - NEWDATE to generate LM(Last Month) and MTD(Month to Date)

Top Replies

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING