on 02-28-2013 12:02 AM
Hell group,
I am attempting to create an aging report in the following layout. Could someone please help me with the Aging calculation that I can perform to determine if an invoice has not been received and is between 1-30 or 31-60 or > 60. How would I do this?
I am thinking of using the DateDiff function to calculate the length of time from the invoice date to the current date. Would this be an appropriate function to use or is there a better way of accomplishing this. I am open to any suggestions or ideas you may have.
I look forward to your input.
thanks in advance.
Imtiaz.
Location | 1-30 Days Aging | 31-60 Days Aging | >60 Days Aging | |||
# of lines | Total Ext Cost | # of lines | Total Ext Cost | # of lines | Total Ext Cost | |
11001 |
|
|
|
HI Imtiaz,
Try this:
Create this formula called "Age" and use it as the column in the crosstab:
if {date_field} in Aged0to30Days then "1-30 Days Ageing" else
if {date_field} in Aged31to60Days then "31-60 Days Ageing" else
if {date_field} in [Aged61to90Days, Over90Days] then ">60 Days Ageing"
Let me know if this works.
-Abhilash
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Abhilash,
I should have been a little more specific regarding what I am attempting to accomplish. This Aging report is to try to determine when an invoiced item was received from the date of the invoice. For example, If the invoice was received today but this item was received into the system 3 month later, then it would fall into the 1-30 bucket and so on.
When you refer to "Aged0to30Days", "Aged31to60Days and so on. It seems that you are asking me to create three formulas for these three categories and then create a formula called "Age" to make this comparison. Is this correct?
Thanks,
Imtiaz.
Hi Abhilash,
The formula you provided works fine, but my quandry is trying figure out how to get a count of the invoices that fall in one of these buckets as well as the total dollar amount of the invoices, grouped by location.
I am not too familiar with using Cross-Tab queries, so I am trying to figure-out things as I go. I don't think I am doing this right. For the row column I am using the loction and the Column column the Age formula you sent.
The output seems to be including all invoices. I think I need to add additional filter criteria to the cross-tab to retrieve only invoices that do not have a receiver and fall into one of the categories. I know how to create this formula, but not sure how to apply this filter criteria to the cross tab query.
Do you have further suggestions as to how I could accomplish this? Please let me know your thoughts on this.
Thanks again for your help.
Imtiaz.
I tried modifying the formula to exclude invoices that have a receipt, but, using this logic I get everything as current. It Seems unlikely that all invoices are of "current status". I need to think this through and see what other filter criteria (s) I may need to add to have it work right. I am close but not just there yet.
If ({APINVOICE.INVOICE_DTE}) in Aged0to30Days and isnull({MAINVDTL.MA_REC_NBR}) then "1-30 Days Aging" else
if ({APINVOICE.INVOICE_DTE}) in Aged31to60Days and isnull({MAINVDTL.MA_REC_NBR}) then "31-60 Days Aging" else
if ({APINVOICE.INVOICE_DTE}) in [Aged61to90Days,Over90Days] and isnull({MAINVDTL.MA_REC_NBR}) then " > 60 Days Aging" else
"Current"
It might be the brackets. Could you try this
If ({APINVOICE.INVOICE_DTE} in Aged0to30Days and isnull({MAINVDTL.MA_REC_NBR})) then "1-30 Days Aging" else
if ({APINVOICE.INVOICE_DTE} in Aged31to60Days and isnull({MAINVDTL.MA_REC_NBR})) then "31-60 Days Aging" else
if ({APINVOICE.INVOICE_DTE} in [Aged61to90Days,Over90Days] and isnull({MAINVDTL.MA_REC_NBR})) then " > 60 Days Aging" else
"Current"
-Abhilash
Hi Imtiaz,
While in the formula editor for this formula, there should be a drop-down on the top that says "Exceptions for NULLS". Could you change that to "Default Values for NULLS" and also remove the isnull() from the formula. Just try:
If ({APINVOICE.INVOICE_DTE} in Aged0to30Days and {MAINVDTL.MA_REC_NBR} = "") then "1-30 Days Aging" else
.
.
Could you also try creating another sample report and create a group on this formula and see if you get any results?
-Abhilash
that did it. It did not like using the empty string so I did it this way:
If ({APINVOICE.INVOICE_DTE} in Aged0to30Days and {MAINVDTL.MA_REC_NBR} = 0 ) then "1-30 Days Aging" else
if ({APINVOICE.INVOICE_DTE} in Aged31to60Days and {MAINVDTL.MA_REC_NBR} = 0 ) then "31-60 Days Aging" else
if ({APINVOICE.INVOICE_DTE} in [Aged61to90Days,Over90Days] and {MAINVDTL.MA_REC_NBR} = 0 ) then " > 60 Days Aging" else
"Current"
Thanks a lot for you kind help. I really appreciate it 🙂
Hi Abhilash,
I have an issue with the Aging Report. I am using the AP Invoice Base Amt by location for the Summary total. So, for example, for location 11001 based on the Aging formula shown below, the total should be 1894.11 based on the detail section based on the same formula. I also created a running total formula based on this formula and I get 1894.11.
IF
(({APINVOICE.INVOICE_DTE} in Aged0to30Days and {MAINVDTL.MA_REC_NBR} = 0 ) OR
({APINVOICE.INVOICE_DTE} in Aged31to60Days and {MAINVDTL.MA_REC_NBR} = 0 ) OR
({APINVOICE.INVOICE_DTE} in [Aged61to90Days,Over90Days] and {MAINVDTL.MA_REC_NBR} = 0 )) THEN TRUE
The output I get from the Cross tab is 1894.11 for 1-30 Days Aging, but I also get 441.99 for 31.60 which I do not see in the detail list (shown below).
1-30 Days Aging | 31-60 Days Aging | ||
Location Total | 7 | 1 | |
1,894.11 | 1,894.11 | 441.99 | |
11001 | 7 | 1 | |
1,894.11 | 1,894.11 | 441.99 |
The Crystal report Detail using the above formula yeilds 1894.11. I don't how it is getting 441.99?
LOCATION | BASE_INV_AMT | INVOICE |
11001 | 4.85 | 32918918 |
11001 | 40.58 | 32907375 |
11001 | 40.58 | 32907375 |
11001 | 40.58 | 32907375 |
11001 | 40.58 | 32907375 |
11001 | 112.32 | 7014328112 |
11001 | 81.25 | 7012819014 |
11001 | 81.25 | 7012819014 |
11001 | 30.07 | 7013990097 |
11001 | 400.00 | 32844719 |
11001 | 254.58 | 7014506356 |
11001 | 254.58 | 7014506356 |
11001 | 254.58 | 7014506356 |
11001 | 254.58 | 7014506356 |
11001 | 3.73 | 32932853 |
1,894.11 |
Please see if you can follow what is causing this hiccup.
thanks,
Imtiaz.
User | Count |
---|---|
84 | |
10 | |
10 | |
10 | |
7 | |
6 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.