Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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);
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.
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 | ||||
…. | ….. |
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,
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);
Concat(if([Create Day]<=Day and (len([Complete Day])=0 or [Complete Day]> Day),[Ticket ID]),',')
for 2nd expression.
Thanks a lot for the solution. Its working perfect.
You are welcome 🙂
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?
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)