AVG of days

[smilingjohn]

Hi All ,

I have to get a average of the days for a ticket (Key)

i have to count of Ticket (Key) for resolved_date and if resolved_date is null

then updated_date ..

How should i get the average of this ?

If(isnull(resolved_date ,updated_date,resolved_date )

Can please someone help me on this ?

Thanks in advance

[PrashantSangle]

On which date are you trying to find avg of ticket

in script create new field which will solve your date issue

like

if(isnull(resolved_date),updated_date,resolved_date) as ticket_date

then in front end use field

1: avg(ticket) or

2: count(ticket) / count(TOTAL<ticket_date> ticket)

Regards,

[dx_anupam]

Hi John,

You can get the Resolved date of ticket using formula mentioned by @Prashant Sangle i.e.

if(isnull(resolved_date),updated_date,resolved_date) as ticket_date

Next step you can floor ticket_date,submit date to convert them in number format.

once these are converted to number you can subtract submit date from resolved date. it will return age of ticket.

now you can perform avg function on it.

Regards,

Anupam 

[smilingjohn]

Hi Anupam,

I have done the first part at script level .

if(isnull(resolved_date),updated_date,resolved_date) as ticket_date

How to get it in the number ?

It should give me the output in days ...not in date format

[dx_anupam]

Hi John

Load *,

Floor(ticket_date) - Floor([Submit Date]) as [ticket_age]

Resident main_table;

Drop main_table;

now at front end you can calculate 

Sum(ticket_age) / count( ticket)

if it help you out kindly mark it correct and close the thread.

Regards,

Anupam

[PrashantSangle]

what is your logic for avg days of ticket???

Regards,

[smilingjohn]

Hi Prashant ,

For every Ticket (Key ) we have a created date ...and resolved date ,updated date ..

I need to know how much days a ticket took to get closed ..

and i want the average of the days for all the tickets ..

Thanks in advance

[PrashantSangle · Accepted solution]

to calculate no of days between ticket date and resolved_date or update_date then

in front end you can use interval()

like

interval(if(isnull(resolved_date),update_date,resolved_date)-ticket_date,'D hh:mm')

If it is not solving your problem then explain explain with some sample data w.r.t. also provide required output to get more accurate result.

Regards,

[dx_anupam]

Hi John,

Load *,

Age(ticket_date,[Submit date] )as ticket_age

   Resident  Order;

   Drop Table Order;

ticket_age will return age of ticket in days.

Regards,

Anupam