How to calculate the time difference from the invoice date to the current date

[Former Member]

     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

[abhilash_kumar · Accepted solution]

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

[Former Member · Accepted solution]

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.

[abhilash_kumar · Accepted solution]

Hi Imtiaz,

Don't create three separate formulas; create one single formula with the code I posted above and see if it shows the right results.

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

Mind my stupidity, but is Aged0to30days, Aged31to60days and Aged61to90days  built-in functions that Crystal recognizes?

[abhilash_kumar · Accepted solution]

Yes that's correct!

-Abhilash

[Former Member · Accepted solution]

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.

[abhilash_kumar · Accepted solution]

How about you add the filter condition in the formula to make it something like this:

if ({date_field} in Aged0to30Days and isnull({Receiver})) then "1-30 Days Ageing" else

.

.

.

You can then have two summary fields - one showing the count and the other showing the Sum.

-Abhilash

[Former Member · Accepted solution]

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"

[abhilash_kumar · Accepted solution]

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

[Former Member · Accepted solution]

that did not do it. I don't see anything wrong with the logic. I am not sure where the hiccup is.

[abhilash_kumar · Accepted solution]

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

[Former Member · Accepted solution]

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 🙂

[abhilash_kumar · Accepted solution]

Nice! I didn't realize that the field was numeric.

Glad I could help. Have a great day!

-Abhilash

[Former Member · Accepted solution]

How can I format the Cross tab so that when exported to excel it is properly formatted to appropriate columns and does not overlap?

[abhilash_kumar · Accepted solution]

I've never been able to neatly export a crosstab to Excel.

Try exporting to Excel Data Only; that's the only thing that comes close to neat.

-Abhilash

[Former Member · Accepted solution]

OK. I'll try that. Thanks again.

[Former Member · Accepted solution]

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.