Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have used the excellent tutorial of Swuehl to calculate cumulated intervals between different dates.
http://community.qlik.com/message/348643
In my case those dates are incidents (application downtime)
I'd like now to capitalize on this basis and extend the analysis by using segmentations (by business and months)
Each application can by used by several businesses.
This means that the duration of an incident can be duplicated for each businesses that is using the application
I have created the following case :
// working days : Mon-Fri 8:00-20:00
// This case doesn’t take into account holidays
SET TimestampFormat = 'M/D/YYYY hh:mm';
Business:
LOAD * INLINE [
Business, Application
Designer, Photoshop
Designer, Outlook
Designer, 3DSMax
Hotliner, Outlook
Hotliner, Skype
];
Incidents:
LOAD * INLINE [
Incident, Start, End, Application
Incident 1, 1/1/2013 10:00, 1/1/2013 14:00 Photoshop
Incident 2, 1/1/2013 12:00, 1/1/2013 15:00, Outlook
Incident 3, 1/1/2013 18:00, 1/2/2013 10:00, Outlook
Incident 5, 1/2/2013 09:00, 1/2/2013 10:00, 3DSMax
Incident 4, 1/1/2013 17:00, 1/2/2013 16:00, Skype
];
The output i'm expecting is the folowing
Month Business Impact
January Designer 09:00
January Hotliner 16:00
I can join my entire code if needed.
Anyone has an idea ?
Thanks and regards
You were almost right. There were however a couple of timestamps that were not interpreted as dates, som I moved a couple of Timestamp#() functions to get it right. Further, I subtracted an "epsilon" from the IncidentEnds. And finally , I added the field Duration. And now it works.
HIC
up !
No one has even exeprienced this case (it must be a standard issue in industry) ?
I would first generate the subintervals using partitioning as described in http://community.qlik.com/docs/DOC-4310.
Once this is done, you can create the following data model, with the field "Duration" as the one you want to sum.
HIC
Hi, your tutorials are awsome !
I have tryed to adapt several steps but I don't get it with intervalmatch, could you please tell me what is going wrong ?
SET ThousandSep=' ';
SET DecimalSep=',';
SET MoneyThousandSep=' ';
SET MoneyDecimalSep=',';
SET MoneyFormat='# ##0,00 €;-# ##0,00 €';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET MonthNames='janv.;févr.;mars;avr.;mai;juin;juil.;août;sept.;oct.;nov.;déc.';
SET DayNames='lun.;mar.;mer.;jeu.;ven.;sam.;dim.';
Business:
LOAD * INLINE [
Business, ApplicationId
Designer, Photoshop
Designer, Outlook
Designer, 3DSMax
Hotliner, Outlook
Hotliner, Skype
];
IncidentsSRC:
LOAD * INLINE [
IncidentNo, Start, End, ApplicationId
Incident 1, 01/01/2013 10:00, 01/01/2013 14:00, Photoshop
Incident 2, 01/01/2013 12:00, 01/01/2013 15:00, Outlook
];
Let vEpsilon = Pow(2,-27);
Let vEndTime = now();
Incidents:
Load *,
hash128(IncidentBegin, IncidentEnd) as IncidentKey;
Load
ApplicationId,
TimeStamp(TimeStamp#(Start,'DD/MM/YYYY hh:mm')) as IncidentBegin,
TimeStamp(TimeStamp#(End,'DD/MM/YYYY hh:mm')) as IncidentEnd
RESIDENT IncidentsSRC
;
TempSubIntervals:
Load distinct ApplicationId, Start as SubIntervalBegin RESIDENT IncidentsSRC;
Load distinct ApplicationId, End as SubIntervalBegin RESIDENT IncidentsSRC;
Drop table IncidentsSRC;
// Create intervals from single dates.
SubIntervals:
Load
hash128(SubIntervalBegin) as SubIntervalKey
, SubIntervalBegin
, If(isnull(previous(SubIntervalBegin))=0,TimeStamp(TimeStamp#(Previous(SubIntervalBegin),'DD/MM/YYYY hh:mm')-$(#vEpsilon)) , ($(#vEndTime))) as SubIntervalEnd
Resident TempSubIntervals
Order By SubIntervalBegin Desc;
Drop Table TempSubIntervals;
// ============ Create the bridge table between the subintervals and the incidents ============
TmpIncidentBridgeTable:
IntervalMatch (SubIntervalBegin)
Load distinct IncidentBegin, IncidentEnd resident Incidents;
IncidentBridgeTable:
Load
hash128(IncidentBegin, IncidentEnd) as IncidentKey,
hash128(SubIntervalBegin) as SubIntervalKey
Resident TmpIncidentBridgeTable;
// ============ Cleanup ============
Drop table TmpIncidentBridgeTable;
You were almost right. There were however a couple of timestamps that were not interpreted as dates, som I moved a couple of Timestamp#() functions to get it right. Further, I subtracted an "epsilon" from the IncidentEnds. And finally , I added the field Duration. And now it works.
HIC
Wonderfull ! thanks alot !