Formulas for Aging Count Report

[vhagerty]

I need some help on a report I'm working on. I use CR9 with SQL (Track-It 7.5). As part of the reports I submit monthly I need a count of open tickets for particular ranges (0, 2-5, 6-10, 11-5, 16-20, 21+, Avg and Total). Instead of pulling this on the date I run this report is it possible to be able to have some way to query an aging report based on a particular date? This would allow me to pull the aging report as of the EOM even if I didnt pull it on that date.

The canned report from Track-It I have been trying to work with has the below.

isnull({TASKS.COMPLETED}) or trim(totext({TASKS.COMPLETED})) = ""
if (CurrentDateTime - {@dtOpenDate}) < 10 then 1 else 0
if (CurrentDateTime - {@dtOpenDate}) > 10 then 1 else 0

Any suggestions?

Virginia

 

[Turkbear]

Hi,
Instead of using the CurrentDateTime function, create a Date type parameter and enter the date you want to use..

Create a formula, like your example, for each interval you need to count and place them in the details,something like:

@2to5days
if ({?DateParam} - {@dtOpenDate}) > 1 and ({?DateParam} - {@dtOpenDate}) < 6 ) then 1 else 0

...Suppress their output and SUM them for each total you need..You can also SUM the SUMs for your total and compute the Average as well.






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[vhagerty]

I tried the below and just came up with zeros in the GF. What am I doing wrong?

Virginia

Group by {tasks.wo_num}

Record Selector
{TASKS.STATUS} <> "Completed"

Date Paramater
{?AgingAsOf}

Formulas
Aging = 0
if ({?Agingasof} - {@dtOpenDate})= 0 then 1 else 0
Aging = 1-5
if ({?agingasof} - {@dtOpenDate}) >0 and ({?agingasof} - {@dtOpenDate}) <6 then 1 else 0
dtOpenDate
UTCtoLocal ({tasks.opendate})

Detail (suppressed)
{@Aging = 0} {@Aging = 1-5}

GF
Sum of Detail formulas

 

[lbass]

You should check to see if your formulas are returning negative values. Your parameter date must be some date greater than your {@dtOpenDate}. You should also verify that there are in fact records that meet these criteria.

-LB

 

[vhagerty]

I'm almost there!! When I removed the grouping I started getting numbers. Now I just need to get an average number of days open to go with this.

Virginia