AVG Days to Pay? Where does it live


(system) #1

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 Gomez
Software 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>&nbsp;</p><p>Jose - it&#39;s a hidden tracker that for some reason exists but isn&#39;t used anywhere that I know&nbsp;of - I can&#39;t tell if it&#39;s behind the Customer Credit Manager&nbsp;- maybe you can with your superior ninja skills.&nbsp;</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

-Chad


On 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>


(system) #2
Anyone know where this is stored? If anywhere?

Jose C Gomez
Software Engineer


T: 904.469.1524 mobile

E: jose@…

http://www.josecgomez.com
     Â


Quis custodiet ipsos custodes?

(system) #3
I believe it is calculated

-Chad


On Thu, Oct 17, 2013 at 3:45 PM, Jose Gomez <jose@...> wrote:

Â
<div>
  
  
  <p></p><div dir="ltr"><div class="ygrps-yiv-1937198786gmail_default" style="font-family:verdana, sans-serif;font-size:small;">Anyone know where this is stored? If anywhere?</div><div><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

E: jose@...

http://www.josecgomez.com
     Â


Quis custodiet ipsos custodes?

</div>
 


<div style="color:#fff;min-height:0;"></div>


(system) #4

LOL! I was just working on this today!

 

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. 

 

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

-Chad


On Thu, Oct 17, 2013 at 3:45 PM, Jose Gomez <jose@...> wrote:

 
<div>
  
  
  <div dir="ltr"><div class="ygrps-yiv-205627962ygrps-yiv-980039787gmail_default" style="font-family:verdana, sans-serif;font-size:small;">Anyone know where this is stored? If anywhere?</div><div><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);min-height:0px;"></div>


(system) #5
Thanks Mike


Jose C Gomez
Software Engineer


T: 904.469.1524 mobile

E: jose@…

http://www.josecgomez.com
     Â


Quis custodiet ipsos custodes?


On Thu, Oct 17, 2013 at 5:12 PM, <mkgross69@...> wrote:

Â
<div>
  
  
  <p></p><p>LOL! I was just working on this today!</p><p> </p><p>Jose - it&#39;s a hidden tracker that for some reason exists but isn&#39;t used anywhere that I know of - I can&#39;t tell if it&#39;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

-Chad


On Thu, Oct 17, 2013 at 3:45 PM, Jose Gomez <jose@…> wrote:

Â
<div>
  
  
  <div dir="ltr"><div class="ygrps-yiv-1529101973im"><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-1529101973im"><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

E: jose@...

http://www.josecgomez.com
     Â


Quis custodiet ipsos custodes?
</div>
 


<div style="color:rgb(255,255,255);"></div>

</div>
 


<div style="color:#fff;min-height:0;"></div>


(system) #6

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 Gomez
Software 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>&nbsp;</p><p>Jose - it&#39;s a hidden tracker that for some reason exists but isn&#39;t used anywhere that I know&nbsp;of - I can&#39;t tell if it&#39;s behind the Customer Credit Manager&nbsp;- maybe you can with your superior ninja skills.&nbsp;</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

-Chad


On Thu, Oct 17, 2013 at 3:45 PM, Jose Gomez <jose@…> wrote:

 
<div>
  
  
  <div dir="ltr"><div class="ygrps-yiv-1909038846ygrps-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-1909038846ygrps-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>


(system) #7

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 Gomez
Software 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>&nbsp;</p><p>Jose - it&#39;s a hidden tracker that for some reason exists but isn&#39;t used anywhere that I know&nbsp;of - I can&#39;t tell if it&#39;s behind the Customer Credit Manager&nbsp;- maybe you can with your superior ninja skills.&nbsp;</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

-Chad


On Thu, Oct 17, 2013 at 3:45 PM, Jose Gomez <jose@…> wrote:

 
<div>
  
  
  <div dir="ltr"><div class="ygrps-yiv-866412017ygrps-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-866412017ygrps-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>


(system) #8

 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 Gomez
Software 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>&nbsp;</p><p>Jose - it&#39;s a hidden tracker that for some reason exists but isn&#39;t used anywhere that I know&nbsp;of - I can&#39;t tell if it&#39;s behind the Customer Credit Manager&nbsp;- maybe you can with your superior ninja skills.&nbsp;</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

-Chad


On Thu, Oct 17, 2013 at 3:45 PM, Jose Gomez <jose@…> wrote:

 
<div>
  
  
  <div dir="ltr"><div class="ygrps-yiv-1715640425ygrps-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-1715640425ygrps-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>