A solution would be to use IntervalMatch of the Open to Closed (or Today() if Closed is null) against a MasterCalendar generating a Flag_Closed, Flag_Open or a State Field with the value of Open or Closed per ticket per day.
This allows to total opened/closed stats for any day, week. month, quarter, year by looking at the last value for a ticket in the selected date range.
So for month end Jan 31, every ticket will have a value of opened or closed; yes this means tickets live forever and the database will grow, but Qlik works fairly quickly with this in my experience.
After a little more time I have solved my problems.
1. Added a test for NULL Date Closed.
2. The date comparison of Date Opened to Date Closed was not working because my dates were timestamped. I resolved this by converting the dates to date only in the load script.
WHERE %KeyDate <= Today();
Date([Date Opened]+iterno()-1) as %KeyDate,
If(iterno()=1, 1,0) as Flag_New,
If(len(trim([Date Closed]))>0, If([Date Opened]+iterno()-1 = [Date Closed],1,0),'0') as Flag_Closed,
If(len(trim([Date Closed]))>0, If([Date Opened]+iterno()-1 <= [Date Closed],1,0),'1') as Flag_Open
Resident [SN Tickets]
WHILE [Date Opened]+iterno()-1 <= [Date Closed];
I have linked this table to my Master Calendar by the %KeyDate field.