Counting Outstanding Open Tickets

[Keitaru]

Hi,

I've been trying this for the longest time but still have yet to be able to achieve my Objective had referred to some examples such as Intervalmatch Count Open Items and 

OBJECTIVE

Combo Chart

Dimension: Month(ReferenceDate)

Measures 1 (Line): Cumulative outstanding (Open) tickets over the past week

Measures 2 (Bar): Ticket Created in that Week/Month

Measures 3 (Bar): Ticket Closed in that Week/Month

Data Model Viewer

My Script:

Data:
LOAD
"Problem ID*+",
Date(Timestamp#("Problem Created Date",'DD/MM/YYY hh:mm:ss')) as "Problem Created Date",
"Operational Categorization Tier 2",
"Status*",
Date(Timestamp#("Closed Date",'DD/MM/YYY hh:mm:ss')) as "Problem Closed Date"

FROM Data;

DateRange:
Load
"Problem ID*+",
"Status*" as TicketStatus,
"Problem Created Date" as Created,
If(not isnull("Problem Closed Date") or len(trim("Problem Closed Date"))<>0 ,"Problem Closed Date",Today()) as Closed

Resident Data;

LinkTable:
LOAD "Problem ID*+",
TicketStatus,
Date(Created+iterno()-1) as ReferenceDate,

If(iterno()=1, 1,0) as CreatedFlag,

If(Date(Created+iterno()-1) = Closed, 1,0) as ClosedFlag

Resident DateRange

WHILE Created+iterno()-1 <= Closed;

MasterCalender:
Load
ReferenceDate,
Month(ReferenceDate) as Month,
Year(ReferenceDate) as Year,
Date(MonthStart(ReferenceDate),'MMM-YY') as Period

Resident LinkTable;

[hic · Accepted solution]

I would use the following script

Data:
LOAD
[Problem ID*+] as ProblemID,
[Status*] as TicketStatus,
Date(Floor([Problem Created Date])) as CreatedDate,
Date(Floor([Problem Closed Date] )) as ClosedDate,
[Operational Categorization Tier 2] as Categorization
FROM [lib://Keitaru/Data.xlsx] (ooxml, embedded labels, table is Sheet1);

Dates:
Load
ProblemID,
Date(CreatedDate+IterNo()-1) as ReferenceDate,
If(IterNo()=1, 1,0) as IsOpeningDate,
If(CreatedDate+IterNo()-1 < ClosedDate, 1,0) as IsOpen,
If(CreatedDate+IterNo()-1 = ClosedDate, 1,0) as IsClosingDate
Resident Data
While CreatedDate+IterNo()-1 <= RangeMin(ClosedDate,Today());

Calendar:
Load
ReferenceDate,
Dual('W' & Num(Week(ReferenceDate),'00') &'-'& Mid(Year(ReferenceDate),3,2),WeekStart(ReferenceDate)) as Week,
Date(MonthStart(ReferenceDate),'MMM-YY') as Month,
Year(ReferenceDate) as Year
Resident Dates;

And define the three measures as 

Count(Distinct {<IsOpen={1}>} [ProblemID]) // # of open tickets

Count(Distinct {<IsOpeningDate={1}>} [ProblemID]) // Opened this period

Count(Distinct {<IsClosingDate={1}>} [ProblemID]) // Closed this period

/HIC

[Keitaru]

Finally! Thank you so much @hic ! 

So now my question is how do I calculate rolling outstanding Tickets. Example if i were to select say certain date, as long as the ticket is still marked as is Open it would count all the tickets prior to the selected date.

Current in expression I've tried using Cumulative however its not showing the right number from what I see:

Rangesum(above(Count(Distinct {<IsOpen={1}>} [ProblemID]), 0, RowNo(Total)))

Looking to have the yellow line at a higher point as it ideally should count in all isOpen tickets that are still currently open the current count Count(Distinct {<IsOpeningDate={1}>} [ProblemID]) doesnt seem to be reflecting this.

[hic]

I think you are making it too complicated... My first expression calculates exactly what you want: It calculates the total number of open tickets that specific date - also if they were created many months ago. I don't think you need to (or should) use any additional accumulation.

HIC

[Keitaru]

Hi @hic 

After reviewing the data and script i think i found the problem. Apparently not all Created dates is triggering IsOpen to add 1 to the data table example a ticket open in 2016 and is still open now IsOpen is showing as 0 against the reference date.

[Keitaru]

Hi HIC,
I've adapted the script for my data a little, which includes the following requirements
If ticket created, isOpening = 1
If Tickets status are not marked as cancelled, and are still open isOpen = 1 (Status = Pending, Assigned, Under Review, Under Investigation, using this to check the data if isOpen shows 1)
If Tickets status are marked as cancelled and do not have a closed date or if Ticket has a closed date, isClosing = 1

Dates:
Load
[Problem ID],
Date(CreatedDate+IterNo()-1) as ReferenceDate,
If([Status]<>'Cancelled' and IterNo()=1, 1,0) as IsOpeningDate,
If([Status]<>'Cancelled' and CreatedDate+IterNo()-1 < ClosedDate, 1,0) as IsOpen,
If([Status]<>'Cancelled'  and CreatedDate+IterNo()-1 = ClosedDate, 1,0) as IsClosingDate
Resident [Open Problem] While CreatedDate+IterNo()-1 <= RangeMin(ClosedDate,Today());

Calendar:
Load
ReferenceDate,
Dual('W' & Num(Week(ReferenceDate),'00') &'-'& Mid(Year(ReferenceDate),3,2),WeekStart(ReferenceDate)) as Week,
Date(MonthStart(ReferenceDate),'MMM-YY') as Month,
Year(ReferenceDate) as Year
Resident Dates;

How to deal if ClosedDate is a Null?