Trial Balance of ServiceNow Ticket Data

[AnalyticsDev]

Hi,

I have a need to build a 'Trial Balance' chart of ServiceNow Ticket data within Qliksense and I'm having difficulty.  For example, For the month of February I need to show:

Beginning Balance of tickets: This essentially would be the End Balance of tickets from January

New: Any NEW ticket created in February

Closed: Any ticket CLOSED in february (could have been created in any prior month)

End Balance: How many tickets were left open at the end of February.

For the next month, March, the beginning balance would need to be February's end balance.  

In addition to above, this needs to be a rolling 13 months, but Im first trying to iron out the Beginning/Ending balance is these two are giving me the most headaches.

I've attached an excel with sample data.

[vinieme12]

As below

Fact:
LOAD
Number,
Date(floor(Created)) as Created,
Date(floor(Closed)) as Closed,
"Record Type",
State,
"Incident state",
Number&Created as Key
FROM [lib://AttachedFiles/SampleDataToQlik.xlsx]
(ooxml, embedded labels, table is Sheet1);

Bridge:
Load Key,Created as Date , 'Created' as DateType
Resident Fact;
Load Key,Closed as Date , 'Closed' as DateType
Resident Fact
Where len(Closed)>1;
Load Key,monthstart(Created,1) as Date , 'CarriedOver' as DateType
Resident Fact;

MasterCalendar:
Load
TempDate AS Date,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
'Q' & ceil(month(TempDate) / 3) AS Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
;

//=== Generate a temp table of dates ===
LOAD
date(mindate + IterNo()) AS TempDate
,maxdate // Used in InYearToDate() above, but not kept
WHILE mindate + IterNo() <= maxdate;

//=== Get min/max dates from Field ===/
LOAD
min(FieldValue('Date', recno()))-1 as mindate,
max(FieldValue('Date', recno())) as maxdate
AUTOGENERATE FieldValueCount('Date');

exit Script;

New

count({<DateType={'Created'},State={'New'}>}distinct Number)

Closed

count({<DateType={'Closed'},State={'Closed'}>}distinct Number)

CarriedOver/ unclosed tickets from previous period

count({<DateType={'CarriedOver'},State-={'Closed'}>}distinct Number)