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

    Display results as of a certain date.

    James Maloy

      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.
          Mark Holtz

          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!