1 Reply Latest reply on May 8, 2013 12:13 PM by Mark Holtz

Display results as of a certain date.

HI, We are creating a report to display statistics that will show how long an item has been in our database separated into 3 month groups. We call it an aging report. We need to show lists of items grouped by month as of a date in the past. So if I have an item that is @ six months old today, but I want to run a report that would show the age of the item as of Jan 1 2013 it would fall in the 2 month grouping. Could you assist with instructions? I searched the KB but maybe wasnt using the right criteria. Thanks for your help.

Thanks

Jim

Calculations

"Invoice Aging Days"

([Start Date])-[Service Dt]

"Invoice Aging Buckets"

IF [Invoice Aging Days] <= 30 THEN '0-30' ELSEIF

[Invoice Aging Days] <= 60 THEN '31-60' ELSEIF

[Invoice Aging Days] <= 90 THEN '61-90' ELSEIF

[Invoice Aging Days] <= 120 THEN '91-120' ELSEIF

[Invoice Aging Days] <= 150 THEN '121-150'  ELSEIF

[Invoice Aging Days] <= 180 THEN '151-180'  ELSEIF

[Invoice Aging Days] <= 210 THEN '181-210'  ELSEIF

[Invoice Aging Days] <= 250 THEN '121-250' ELSEIF

[Invoice Aging Days] <= 280 THEN '251-280' ELSEIF

[Invoice Aging Days] <= 310 THEN '281-310' ELSEIF

[Invoice Aging Days] <= 330 THEN '311-330' ELSEIF

[Invoice Aging Days] <= 365 THEN '330-365' ELSE 'Over 365'

END

• 1. Re: Display results as of a certain date.

Hi James,

Sorry, looks like this one fell into the cracks of the Forum. To do what you're asking, you can use a parameter + calculated field construct. The parameter is easy: "As Of Date" (Date-Time data type)

Depending on how you want to set up your age buckets (you show 30-day buckets in your sample data, but mention "2 months old" in your write-up), you then create a calculated field to group the age. The "2 months" will be imprecise because with date math, something created 1/31/2013 will be "2 months old " on 3/1/2013...

AgeInDays: IF [InvoiceDate] > [AsOfDateParameter] THEN 'Invoice Not Created Yet'
ELSEIF DATEDIFF('day',[InvoiceDate],[AsOfDateParameter]) <= 30 THEN '0 - 30 Days'
ELSEIF DATEDIFF('day',[InvoiceDate],[AsOfDateParameter]) <= 60 THEN '0 - 60 Days'...
ELSEIF DATEDIFF('day',[InvoiceDate],[AsOfDateParameter]) <= 365 THEN '330 - 365 Days'
ELSE 'Over 365 Days' END

AgeInMonths: IF [InvoiceDate] > [AsOfDateParameter] THEN 'Invoice Not Created Yet'
ELSEIF DATEDIFF('month',[InvoiceDate],[AsOfDateParameter]) > 12 THEN 'Over 12 months'
ELSE STR(DATEDIFF('month',[InvoiceDate],[AsOfDateParameter]) )+ ' months'
END

Better late than never, I guess. Hope that helps!