formula for throughput calculation

[thisisboni]

Environ: CR10, Oracle 9i

table in dB called THROUGHPUT_DATA with columns

indicator, ticket#, date, yyyy_mm

indicator is either c or R

C = create, R = resolved


Backlog for each month = ticket created prior to that month and resolved after that month or is still not resolved.

say: create_dt = 5/3/2006 and resolved_date = 7/4/2006

then its a backlog for 2006_06 and 2006_07

I have the following formula:

//{@backlog}

datevar dt:= date(tonumber(left(GroupName ({THROUGHPUT_DATA.YYYY_MM}),4)),tonumber(right(GroupName ({THROUGHPUT_DATA.YYYY_MM}),2)),01);

IF (({%CREATE_DATE}< dt and isnull({THROUGHPUT_DATA.YYYY_MM})) OR
({%CREATE_DATE}< dt and {%RESOLVED_DATE}>=dt)) THEN
{THROUGHPUT_DATA.CASEID} + ' Backlog'
ELSE {THROUGHPUT_DATA.CASEID} + ' Not a Backlog'



The problem with this is: say for the sample data above its showing it as a backlog for 2006_07 but not as 2006_06

Please let me know if more info is needed and/or a possible solution to this.

Thanks

 

[synapsevampire]

What is %create_date?

That can't be your formula, % is for SQL Expressions.

Then you reference these columns in the table:

indicator, ticket#, date, yyyy_mm

Yet your formula references:

{THROUGHPUT_DATA.YYYY_MM}

So, which is it?

Assuming %create_date iks a valid sql expression for the date, try:

whileprintingrecords;
datevar start_dt := date(tonumber(left(GroupName ({THROUGHPUT_DATA.YYYY_MM}),4)),tonumber(right(GroupName ({THROUGHPUT_DATA.YYYY_MM}),2)),01);
datevar end_dt := dateserial(tonumber(left(GroupName ({THROUGHPUT_DATA.YYYY_MM}),4)),tonumber(right(GroupName ({THROUGHPUT_DATA.YYYY_MM}),2))+1,1);
IF {%CREATE_DATE} < start_dt and {%CREATE_DATE} >= end_dt THEN
{THROUGHPUT_DATA.CASEID} + ' Backlog'
ELSE
{THROUGHPUT_DATA.CASEID} + ' Not a Backlog'

-k

 

[thisisboni]

yeah: this table THROUGHPUT_DATA is derived from the main table helpdesk table ..

I join these 2 table to extract the create_date and resolved_date from the helpdesk table (sql expression needed as dates are stored in epoch : the sql expressions work fine)

your formula is in the lines that I was proceeding with .. (only the 2nd sql expression would be {%RESOLVED_DATE} instead of {%CREATE_DATE})

but the thing is I need the backlog to appead on each yyyy_mm value after its created till it is resolved
eg. ticketid --> T123
create_dt = 5/3/2006 and resolved_date = 7/4/2006

then t123 a backlog for 2006_06 and 2006_07

with this approach it will only be a backlog for 2007_07