Hi All, My aim is to get ‘Spend Month’ and ‘Prior Month’ amounts on the report.Based on the given dates iam able to get the ‘Spend Month’ amount but not the ‘Prior Month’ amount.How to achieve this? Best regards Hari
Calculate the start and end dates of the prior period using CALCDATE(). The date formula can -1M or or -30D or … You could also use the System Calendar table and search for previous with FIND().
Hi Jon, Firstly, my apologies for the late reply.With the below codes,iam able to get the output only for the “Spend Month” by giving “Start & End Dates”.How to get both “Spend Month” & “Prior Spend” by giving “Start & End Dates”. “spend month”:=0; “prior spend”:=0; priorspendenddate:=CALCDATE('-1D',startdate); IF "fa ledger entry".FIND('-') THEN BEGIN REPEAT "fa ledger entry".SETRANGE("fa ledger entry"."FA Posting Date",010199D,priorspendenddate); IF ("Main Asset Component"."FA No."="fa ledger entry"."FA No.")AND("fa ledger entry"."FA Posting Type"=0) THEN "prior spend":="prior spend"+"fa ledger entry".Amount; UNTIL "fa ledger entry".NEXT=0; END; IF "fa ledger entry".FIND('-') THEN BEGIN REPEAT "fa ledger entry".SETRANGE("fa ledger entry"."FA Posting Date",startdate,enddate); IF ("Main Asset Component"."FA No."="fa ledger entry"."FA No.")AND("fa ledger entry"."FA Posting Type"=0) THEN "spend month":="spend month"+"fa ledger entry".Amount; UNTIL "fa ledger entry".NEXT=0; END; Best regards, Hari
Hari, I think your code is on the right track, but the SETRANGE should be outside the REPEAT … UNTIL loop for it to work properly. As written, the second loop finds the first entry in the current period and then filters it out so that NEXT doesn’t see the rest. Just pull the SETRANGE out as follows and it should work. “spend month”:=0; “prior spend”:=0; priorspendenddate:=CALCDATE(’-1D’,startdate); // this line has moved “fa ledger entry”.SETRANGE(“fa ledger entry”.“FA Posting Date”,010199D,priorspendenddate); IF “fa ledger entry”.FIND(’-’) THEN BEGIN REPEAT IF (“Main Asset Component”.“FA No.”=“fa ledger entry”.“FA No.”)AND(“fa ledger entry”.“FA Posting Type”=0) THEN “prior spend”:=“prior spend”+“fa ledger entry”.Amount; UNTIL “fa ledger entry”.NEXT=0; END; // This line has moved “fa ledger entry”.SETRANGE(“fa ledger entry”.“FA Posting Date”,startdate,enddate); IF “fa ledger entry”.FIND(’-’) THEN BEGIN REPEAT IF (“Main Asset Component”.“FA No.”=“fa ledger entry”.“FA No.”)AND(“fa ledger entry”.“FA Posting Type”=0) THEN “spend month”:=“spend month”+“fa ledger entry”.Amount; UNTIL “fa ledger entry”.NEXT=0; END;
Hi Milton, Thanks.Its works fine. How to get group total on “prior spend” and “spend month” and this is being grouped on “main asset no.” Best regards, Hari
My sincere thanks to both Jonathan and Milton.I got a solution for the above. Best regards, Hari