In the BAQ - bring in the Invoice date and PayDate(?) fields, and calc the difference in the dates. This will be days-to-pay-per-invoice. Then on your report, which I have to assume is Customer w/Invoice detail, add the avg(days-to-pay-per-invoice) calculation in the group header (or footer) where you have the customer name.
Mike
---In vantage@yahoogroups.com, <devind@...> wrote:Best place to calculate the date diff i am using a BAQ report for this.
---In vantage@yahoogroups.com, <mkgross69@...> wrote:I suspect you would have to bring in the invoice detail and use the datediff to calculate a field for "days to pay" per invoice. Then hide the section on the report with the invoice detail and the AvgDaysToPay would be avg(the datediff calculated field) in any summary/header/footer section.
---In vantage@yahoogroups.com, <devind@...> wrote:has any one done this in a crystal report tied to a progress backend or in a BAQ i am running Epicor 905702 x64
---In vantage@yahoogroups.com, <vantage@yahoogroups.com> wrote:Thanks Mike
Jose C GomezSoftware Engineer
T: 904.469.1524 mobile
Quis custodiet ipsos custodes?On Thu, Oct 17, 2013 at 5:12 PM, <mkgross69@...> wrote:<div> <p>LOL! I was just working on this today!</p><p> </p><p>Jose - it's a hidden tracker that for some reason exists but isn't used anywhere that I know of - I can't tell if it's behind the Customer Credit Manager - maybe you can with your superior ninja skills. </p>
I used the ARAgingTackerAdapter. I just added the YTDAvgDaysToPay field to my Customer Credit Manager about an hour ago. It's simple enough to calculate as well..
select custid
, name
, isnull(avg(datediff(d,invoicedate,closeddate)),0) as avgdaystopay
, sum(invoicebal) as openbalance
from invchead join customer on invchead.company=customer.company and
invchead.custnum=customer.custnum
where creditmemo = 0
group by custid, name
having sum(invoicebal) > 0
order by name
This code calculates the same numbers as the results in the tracker. Hope that helps.
Mike
---In vantage@yahoogroups.com, <chad.villa@...> wrote:
I believe it is calculated-ChadOn Thu, Oct 17, 2013 at 3:45 PM, Jose Gomez <jose@…> wrote:
<div> <div dir="ltr"><div class="ygrps-yiv-1958360007ygrps-yiv-207274784ygrps-yiv-481009069ygrps-yiv-2123213562ygrps-yiv-688184217im"><div style="font-family:verdana, sans-serif;font-size:small;">Anyone know where this is stored? If anywhere?</div></div><div><div class="ygrps-yiv-1958360007ygrps-yiv-207274784ygrps-yiv-481009069ygrps-yiv-2123213562ygrps-yiv-688184217im"><div><font face="verdana, sans-serif"><br><font color="#333333"><b>Jose C Gomez</b></font></font></div>Software Engineer
T: 904.469.1524 mobile
Quis custodiet ipsos custodes?</div> <div style="color:rgb(255, 255, 255);"></div></div> <div style="color:rgb(255, 255, 255);min-height:0px;"></div>
