I have created a canonical calendar to link to the Date Raised and Date Resolved fields in my extract as below.
DateLink:
// TR Extract Date Raised
LOAD
[TR #]
,DateRaisedasDate// Fact Date
,'Created' asDateType// Fact Type
RESIDENT Resident_TR_Dump
;
// TR Extract Date Resolved
LOAD
[TR #]
,DateResolvedasDate
,'Resolved' asDateType
RESIDENT Resident_TR_Dump
;
CALLCalendarFromField('Date', 'CommonCalendar', '');
CALLCalendarFromField('DateRaised', 'CreatedCalendar', 'Created ');
CALLCalendarFromField('DateResolved', 'ResolvedCalendar', 'Resolved ');
This calls the below calendar routine
//===================================================
// Subroutine to Generate Calendar.
//===================================================
SUB CalendarFromField(_field, _calendar, _prefix)
[$(_calendar)]:
// Generate Final Calendar
LOAD
[$(_field)]
,year($(_field)) as [$(_prefix)Year]
,MonthName([$(_field)]) as [$(_prefix)Month]
,day([$(_field)]) as [$(_prefix)Day]
,WeekStart([$(_field)]) as [$(_prefix)Weekstart]
;
// Generate range of dates between min and max.
LOAD
date(DateMin + IterNo()) as [$(_field)] // Link Field
WHILE DateMin + IterNo() <= DateMax
;
// Find min and max of date field values.
LOAD
min(datefield)-1 as DateMin
,max(datefield) as DateMax
;
// Load date field values.
LOAD
FieldValue('$(_field)', RecNo()) as datefield
AutoGenerate FieldValueCount('$(_field)');
END SUB
Besides the Created and Resolved tickets I need to calculate the Outstanding tickets.
Outstanding tickets are all tickets with Status ='Open'.
I have used the Weekstart for the X axis and use the following expression count(DISTINCT{<Status={'Open'},[Referred to CPS]={'Y'}>}[TR #]) . Essentially it needs to show all tickets that are having status ='Open' even if there is a resolved date for all successive weeks until the Status is changed to Closed as of date. so if a ticket has a Open status that was created in 2015 it needs to reflect in the weekstart of 13 Oct 2016.
Issue that I am facing is that ticket is reflected only in the week that it has been created and in the week that it has been resolved. It is not being considered in the missing weeks between nor in subsequent weeks till date. Will somebody be able to assist me or guide me in the right direction?
