Trial Balance of ServiceNow Ticket Data

AnalyticsDev — Wed, 18 May 2022 21:38:38 GMT

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.

Re: Trial Balance of ServiceNow Ticket Data

vinieme12 — Thu, 19 May 2022 06:46:04 GMT

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)

topic Re: Trial Balance of ServiceNow Ticket Data in App Development

Trial Balance of ServiceNow Ticket Data

Re: Trial Balance of ServiceNow Ticket Data