Aging Buckets - Days 2

[MiagiSan]

Hello,

I am using CRX with SQL and I am trying to add a cross tab to my report with the following information.

For tickets that are not equal to "Closed"
1-3 Days old
3-5 Days old
5-9 Days old
> 9 Days old

I found this posting by LBass a while back but the "aged0to30days" formulas do not work for the number of days I am looking for.

if {table.duedate} in Aged0to30Days then "Aged 0 to 30 Days" else
if {table.duedate} in Aged31to60Days then "Aged 31 to 60 Days" else
if {table.duedate} in Aged61to90Days then "Aged 61 to 90 Days" else
if {table.duedate} in Over90Days then "Over 90 Days"


Here is the formula I tried for 1-3 days.

if {STATUS_TBL.STATUS}<>"CLOSED"
and {CASE.ROW_ADDED_DTTM}>=currentdate-day(currentdate)+2 then 1 else 0

For some reason this formula is giving me a count of 1 for every open ticket, no matter what the age.

I appreciate any help you can offer.

Thank you

R

 

[jdemmi]

Here's how I did something similar -- it may be the long way around but it worked.

I created a formula that counted the number of days between todays date and my datefield.

(like this)
today-{CASE.ROW_ADDED_DTTM}

I placed this formula in the COLUMN section of the cross-tab expert and manually specified/defined the group options creating a bucket for each grouping I required (for me it was 1 day, 2 to 15 days, 16 to 29, 30 to 59, 60 to 89, and 90 or more days)

In the cross tab expert I then defined the summary as a count of the record ID(in my case, task_id) -- in your case I am guessing this would be the call ID

Does this make sense?

-- Jason
"It's Just Ones and Zeros

 

[kskid]

Try this

{STATUS_TBL.STATUS}<>"CLOSED"
and
(
if {CASE.ROW_ADDED_DTTM}>= currentdate-3 then "0-3 Days"
else if {CASE.ROW_ADDED_DTTM}in currentdate-6 to currentdate-4 then "4-6 Days"
else if {CASE.ROW_ADDED_DTTM}in currentdate-9 to currentdate-7 then "7-9 Days"
else
">9 Days"
)

 

[MiagiSan]

Thank you to both!

I tried kskid's suggestion first as it looked the easiest.

The formula works without this:

{STATUS_TBL.STATUS}<>"CLOSED"
and

but when I add this to the formula, I am being asked for a boolean

I tried tweaking the formula but it is still not working. I think I am much closer than I was; I appreciate any more help you can offer.

Cheers

R

 

[lbass]

if {STATUS_TBL.STATUS}<>"CLOSED" then
(
if {CASE.ROW_ADDED_DTTM}>= currentdate-3 then "0-3 Days"
else if {CASE.ROW_ADDED_DTTM}in currentdate-6 to currentdate-4 then "4-6 Days"
else if {CASE.ROW_ADDED_DTTM}in currentdate-9 to currentdate-7 then "7-9 Days"
else
">9 Days"
)

But you would better off using a record selection formula of:

{STATUS_TBL.STATUS}<>"CLOSED"

-LB

 

[MiagiSan]

Perfect!!

Thank you lbass and everyone!