Daily Ticket Stock

[vesiletaskiran]

I have model with 3 fields. 

Ticket Id , Create Day, Complete Day.

I need to count daily open tickets.  So the output will be 

Day , #of open Tickets. (Create Day>=Day and (Complete Day<Day or Complete Day is Null)

I have tried for each ticket creating a  record with while loop to list open days . And aggregating this after daily. This solution created millions of records because of my high data volume

LOAD
TicketID,
(create_day+iterno()-1) as OpenDate
resident Data3PP
WHILE create_day<complete_day;

Would you recommend an optimal solution for that? 

Thanks in advance. 

[jyothish8807]

Hi vesiletaskiran,

Can you please share a sample data for reference ? Also what od you mean by 'Day' ? Is it today's date ?

You can get a list of open tickets by fetching count of tickets which do not have a complete Day.

[vesiletaskiran]

Sorry for the missing information, 

I need the count for every calendar day. Every calendar day count ticket which were created and not completed. 

Below is a sample data with output

Data				Output
Ticket ID	Create Day	Complete Day		Day	#of Open Tickets	Note
T1	1-Nov-18			1-Nov-18	1	T1
T2	5-Nov-18	15-Nov-18		2-Nov-18	1	T1
T3	3-Nov-18	6-Nov-18		3-Nov-18	3	T1,T3,T5
T4	10-Nov-18	12-Nov-18		4-Nov-18	3	T1,T3,T5
T5	3-Nov-18			5-Nov-18	4	T1,T2,T3,T5
				6-Nov-18	3	T1,T2,T5
				7-Nov-18	3	T1,T2,T5
				8-Nov-18	3	T1,T2,T5
				9-Nov-18	3	T1,T2,T5
				10-Nov-18	4	T1,T2,T4,T5
				11-Nov-18	4	T1,T2,T4,T5
				12-Nov-18	3	T1,T2,T5
				….	…..

[dpduran]

Hi @vesiletaskiran

I assume you want the count of days a ticket's been opened...

use the function Interval between complete and Create Dates.... if there is no Complete date then you can use Today's date to calculate days.

In the model I would flag those still open or not checking the CompleteDay fieldgenerating a field called Status.

if(LEN(CompleteDay)>1, 'CLOSED', 'OPEN') as Status

Then with a precedent load you can calculate the aging field which will give you the count of days.

if(Status='CLOSED', interval([Complete Day]-[Create Day], 'D'), interval(Today()-[Create Day], 'D') as Aging

That will give you 2 new columns, 1 with status, another one with the count of days it was or still is open.

Kind regards,

[jyothish8807 · Accepted solution]

Hi,

Try this:

count(if([Create Day]<=Day and (len([Complete Day])=0 or [Complete Day]> Day),[Ticket ID]))

Also refer the script.

A:
Load
[Ticket ID] as [Ticket ID],
date(date#([Create Day],'DD-MMM-YY'),'DD-MMM-YY') as [Create Day],
date(date#([Complete Day],'DD-MMM-YY'),'DD-MMM-YY') as [Complete Day];
LOAD * INLINE [
Ticket ID, Create Day, Complete Day
T1, 1-Nov-18,
T2, 5-Nov-18, 15-Nov-18
T3, 3-Nov-18, 6-Nov-18
T4, 10-Nov-18, 12-Nov-18
T5, 3-Nov-18,
];

MaxMin:
Load
min([Create Day]) as MinDate,
max([Create Day]) as MaxDate
resident A;

Let vMindate=num(peek('MinDate'));
Let vMaxdate=num(peek('MaxDate'));

Temp_Calendar:
Load
$(vMindate) +IterNo()-1 as Num,
date($(vMindate) +IterNo()-1,'DD-MMM-YY') as Day
AutoGenerate 1
While $(vMindate) +IterNo()-1 <=$(vMaxdate);

[jyothish8807]

Concat(if([Create Day]<=Day and (len([Complete Day])=0 or [Complete Day]> Day),[Ticket ID]),',')

for 2nd expression.

[vesiletaskiran]

Thanks a lot for the solution. Its working perfect. 

[jyothish8807]

You are welcome 🙂

[vesiletaskiran]

Hi Jyothish , Is there a way to calculate this on the script. on chart it takes too long to load because of high data volume?

[jyothish8807]

Hi Vesi,

Sorry for the late reply, Please try this:

A:
Load
[Ticket ID] as [Ticket ID],
date(date#([Create Day],'DD-MMM-YY'),'DD-MMM-YY') as [Create Day],date(date#([Create Day],'DD-MMM-YY'),'DD-MMM-YY') as [Day],
date(date#([Complete Day],'DD-MMM-YY'),'DD-MMM-YY') as [Complete Day];
LOAD * INLINE [
Ticket ID, Create Day, Complete Day
T1, 1-Nov-18,
T2, 5-Nov-18, 15-Nov-18
T3, 3-Nov-18, 6-Nov-18
T4, 10-Nov-18, 12-Nov-18
T5, 3-Nov-18,
];

MaxMin:
Load
min([Create Day]) as MinDate,
max([Create Day]) as MaxDate
resident A;

Let vMindate=num(peek('MinDate'));
Let vMaxdate=num(peek('MaxDate'));

Temp_Calendar:
Load
$(vMindate) +IterNo()-1 as Num,
date($(vMindate) +IterNo()-1,'DD-MMM-YY') as Day
AutoGenerate 1
While $(vMindate) +IterNo()-1 <=$(vMaxdate);

Noconcatenate

Cal:

Load

Date

Resident Temp_Calendar;

Drop table Temp_Calendar

Left join (Cal)

Main:

Load

*,

if([Create Day]<=Day and (len([Complete Day])=0 or [Complete Day]> Day),[Ticket ID]) as NewField

resident A;

Drop table A;

At UI try:

count(NewField)