topic Unavailability/Elapsed time Calculation in App Development https://community.qlik.com/t5/App-Development/Unavailability-Elapsed-time-Calculation/m-p/2014682#M83845 <P>Hi Team,</P> <P>I have the below raw data - Incident number, its open time and its resolved time. I need to calculate the unavailability of a device linked to incident based on few conditions as mentioned below.</P> <TABLE dir="ltr" border="1" cellspacing="0" cellpadding="0"><COLGROUP><COL width="48" /><COL width="57" /><COL width="163" /><COL width="163" /></COLGROUP> <TBODY> <TR> <TD data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;Device&quot;}">Device</TD> <TD data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;Incident &quot;}">Incident</TD> <TD data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;Open date &quot;}">Open date</TD> <TD data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;Closed date&quot;}">Closed date</TD> </TR> <TR> <TD data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;Server&quot;}">Server</TD> <TD data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;INC001&quot;}">INC001</TD> <TD data-sheets-value="{&quot;1&quot;:3,&quot;3&quot;:44902.53125}" data-sheets-numberformat="{&quot;1&quot;:7,&quot;2&quot;:&quot;m/d/yyyy h:mm:ss am/pm&quot;,&quot;3&quot;:1}">12/7/2022 12:45:00 PM</TD> <TD data-sheets-value="{&quot;1&quot;:3,&quot;3&quot;:44903.618055555555}" data-sheets-numberformat="{&quot;1&quot;:7,&quot;2&quot;:&quot;m/d/yyyy h:mm:ss am/pm&quot;,&quot;3&quot;:1}">12/8/2022 2:50:00 PM</TD> </TR> <TR> <TD data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;Server&quot;}">Server</TD> <TD data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;INC002&quot;}">INC002</TD> <TD data-sheets-value="{&quot;1&quot;:3,&quot;3&quot;:44903.53125}" data-sheets-numberformat="{&quot;1&quot;:7,&quot;2&quot;:&quot;m/d/yyyy h:mm:ss am/pm&quot;,&quot;3&quot;:1}">12/8/2022 12:45:00 PM</TD> <TD data-sheets-value="{&quot;1&quot;:3,&quot;3&quot;:44908.40625}" data-sheets-numberformat="{&quot;1&quot;:7,&quot;2&quot;:&quot;m/d/yyyy h:mm:ss am/pm&quot;,&quot;3&quot;:1}">12/13/2022 9:45:00 AM</TD> </TR> </TBODY> </TABLE> <P>&nbsp;</P> <P>Conditions:</P> <P>&gt; Business hours to be between 7 AM to 7 PM&nbsp;</P> <P>&gt; Weekends to be excluded</P> <P>Desired Output:</P> <TABLE dir="ltr" border="1" cellspacing="0" cellpadding="0"><COLGROUP><COL width="48" /><COL width="57" /><COL width="163" /><COL width="163" /><COL width="123" /><COL width="479" /></COLGROUP> <TBODY> <TR> <TD data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;Device&quot;}">Device</TD> <TD data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;Incident &quot;}">Incident</TD> <TD data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;Open date &quot;}">Open date</TD> <TD data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;Closed date&quot;}">Closed date</TD> <TD data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;Output to achieve - &quot;}">Output to achieve -</TD> <TD data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;Details&quot;}">Details</TD> </TR> <TR> <TD data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;Server&quot;}">Server</TD> <TD data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;INC001&quot;}">INC001</TD> <TD data-sheets-value="{&quot;1&quot;:3,&quot;3&quot;:44902.53125}" data-sheets-numberformat="{&quot;1&quot;:7,&quot;2&quot;:&quot;m/d/yyyy h:mm:ss am/pm&quot;,&quot;3&quot;:1}">12/7/2022 12:45:00 PM</TD> <TD data-sheets-value="{&quot;1&quot;:3,&quot;3&quot;:44903.618055555555}" data-sheets-numberformat="{&quot;1&quot;:7,&quot;2&quot;:&quot;m/d/yyyy h:mm:ss am/pm&quot;,&quot;3&quot;:1}">12/8/2022 2:50:00 PM</TD> <TD data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;14:05 hours&quot;}">14:05 hours</TD> <TD data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;12/7/2022 12:45:00 PM ---&gt; 6:15 hours (12:45PM to 7:00 PM (Business hours))\r\n12/8/2022 2:50:00 PM ---&gt; 7:50 hours (7:00AM(Business hours) to 2:50PM)\r\n\r\nTotal : 14:05 hours (6:15 + 7:50)&quot;}">12/7/2022 12:45:00 PM ---&gt; 6:15 hours (12:45PM to 7:00 PM (Business hours)) <BR />12/8/2022 2:50:00 PM ---&gt; 7:50 hours (7:00AM(Business hours) to 2:50PM) <BR /><BR />Total : 14:05 hours (6:15 + 7:50)</TD> </TR> <TR> <TD data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;Server&quot;}">Server</TD> <TD data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;INC002&quot;}">INC002</TD> <TD data-sheets-value="{&quot;1&quot;:3,&quot;3&quot;:44903.53125}" data-sheets-numberformat="{&quot;1&quot;:7,&quot;2&quot;:&quot;m/d/yyyy h:mm:ss am/pm&quot;,&quot;3&quot;:1}">12/8/2022 12:45:00 PM</TD> <TD data-sheets-value="{&quot;1&quot;:3,&quot;3&quot;:44908.40625}" data-sheets-numberformat="{&quot;1&quot;:7,&quot;2&quot;:&quot;m/d/yyyy h:mm:ss am/pm&quot;,&quot;3&quot;:1}">12/13/2022 9:45:00 AM</TD> <TD data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;33 hours&quot;}">33 hours</TD> <TD data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;12/8/2022 12:45:00 PM ---&gt; 6:15 hours ( 12:45PM to 7:00 PM (Business hour))\n12/9/2022 ---&gt; 12 hours (Fullday- 7AM to 7PM business hours)\n12/10/2022 ---&gt; 0 (weekend)\n12/11/2022 ---&gt; 0 (weekend)\n12/12/2022 ---&gt; 12 hours (Fullday- 7AM to 7PM business hours)\n12/13/2022 9:45:00 AM ---&gt; 2:45 hours ( 7AM(Business hour) to 9:45AM)\n\nTotal : 33 hours (6:15 + 12 + 0 + 0 + 12 + 2:45)&quot;}">12/8/2022 12:45:00 PM ---&gt; 6:15 hours ( 12:45PM to 7:00 PM (Business hour))<BR />12/9/2022 ---&gt; 12 hours (Fullday- 7AM to 7PM business hours)<BR />12/10/2022 ---&gt; 0 (weekend)<BR />12/11/2022 ---&gt; 0 (weekend)<BR />12/12/2022 ---&gt; 12 hours (Fullday- 7AM to 7PM business hours)<BR />12/13/2022 9:45:00 AM ---&gt; 2:45 hours ( 7AM(Business hour) to 9:45AM)<BR /><BR />Total : 33 hours (6:15 + 12 + 0 + 0 + 12 + 2:45)</TD> </TR> </TBODY> </TABLE> <P>&nbsp;</P> <P>This will tell me that the device Server was unavailable for 47 minutes for that month.</P> <P>&nbsp;</P> <P>Would appreciate any help on this <span class="lia-unicode-emoji" title=":slightly_smiling_face:">🙂</span></P> <P>Regards,</P> <P>Hitha Dhani</P> Fri, 09 Dec 2022 09:04:00 GMT hitha1512 2022-12-09T09:04:00Z Unavailability/Elapsed time Calculation https://community.qlik.com/t5/App-Development/Unavailability-Elapsed-time-Calculation/m-p/2014682#M83845 <P>Hi Team,</P> <P>I have the below raw data - Incident number, its open time and its resolved time. I need to calculate the unavailability of a device linked to incident based on few conditions as mentioned below.</P> <TABLE dir="ltr" border="1" cellspacing="0" cellpadding="0"><COLGROUP><COL width="48" /><COL width="57" /><COL width="163" /><COL width="163" /></COLGROUP> <TBODY> <TR> <TD data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;Device&quot;}">Device</TD> <TD data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;Incident &quot;}">Incident</TD> <TD data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;Open date &quot;}">Open date</TD> <TD data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;Closed date&quot;}">Closed date</TD> </TR> <TR> <TD data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;Server&quot;}">Server</TD> <TD data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;INC001&quot;}">INC001</TD> <TD data-sheets-value="{&quot;1&quot;:3,&quot;3&quot;:44902.53125}" data-sheets-numberformat="{&quot;1&quot;:7,&quot;2&quot;:&quot;m/d/yyyy h:mm:ss am/pm&quot;,&quot;3&quot;:1}">12/7/2022 12:45:00 PM</TD> <TD data-sheets-value="{&quot;1&quot;:3,&quot;3&quot;:44903.618055555555}" data-sheets-numberformat="{&quot;1&quot;:7,&quot;2&quot;:&quot;m/d/yyyy h:mm:ss am/pm&quot;,&quot;3&quot;:1}">12/8/2022 2:50:00 PM</TD> </TR> <TR> <TD data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;Server&quot;}">Server</TD> <TD data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;INC002&quot;}">INC002</TD> <TD data-sheets-value="{&quot;1&quot;:3,&quot;3&quot;:44903.53125}" data-sheets-numberformat="{&quot;1&quot;:7,&quot;2&quot;:&quot;m/d/yyyy h:mm:ss am/pm&quot;,&quot;3&quot;:1}">12/8/2022 12:45:00 PM</TD> <TD data-sheets-value="{&quot;1&quot;:3,&quot;3&quot;:44908.40625}" data-sheets-numberformat="{&quot;1&quot;:7,&quot;2&quot;:&quot;m/d/yyyy h:mm:ss am/pm&quot;,&quot;3&quot;:1}">12/13/2022 9:45:00 AM</TD> </TR> </TBODY> </TABLE> <P>&nbsp;</P> <P>Conditions:</P> <P>&gt; Business hours to be between 7 AM to 7 PM&nbsp;</P> <P>&gt; Weekends to be excluded</P> <P>Desired Output:</P> <TABLE dir="ltr" border="1" cellspacing="0" cellpadding="0"><COLGROUP><COL width="48" /><COL width="57" /><COL width="163" /><COL width="163" /><COL width="123" /><COL width="479" /></COLGROUP> <TBODY> <TR> <TD data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;Device&quot;}">Device</TD> <TD data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;Incident &quot;}">Incident</TD> <TD data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;Open date &quot;}">Open date</TD> <TD data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;Closed date&quot;}">Closed date</TD> <TD data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;Output to achieve - &quot;}">Output to achieve -</TD> <TD data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;Details&quot;}">Details</TD> </TR> <TR> <TD data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;Server&quot;}">Server</TD> <TD data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;INC001&quot;}">INC001</TD> <TD data-sheets-value="{&quot;1&quot;:3,&quot;3&quot;:44902.53125}" data-sheets-numberformat="{&quot;1&quot;:7,&quot;2&quot;:&quot;m/d/yyyy h:mm:ss am/pm&quot;,&quot;3&quot;:1}">12/7/2022 12:45:00 PM</TD> <TD data-sheets-value="{&quot;1&quot;:3,&quot;3&quot;:44903.618055555555}" data-sheets-numberformat="{&quot;1&quot;:7,&quot;2&quot;:&quot;m/d/yyyy h:mm:ss am/pm&quot;,&quot;3&quot;:1}">12/8/2022 2:50:00 PM</TD> <TD data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;14:05 hours&quot;}">14:05 hours</TD> <TD data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;12/7/2022 12:45:00 PM ---&gt; 6:15 hours (12:45PM to 7:00 PM (Business hours))\r\n12/8/2022 2:50:00 PM ---&gt; 7:50 hours (7:00AM(Business hours) to 2:50PM)\r\n\r\nTotal : 14:05 hours (6:15 + 7:50)&quot;}">12/7/2022 12:45:00 PM ---&gt; 6:15 hours (12:45PM to 7:00 PM (Business hours)) <BR />12/8/2022 2:50:00 PM ---&gt; 7:50 hours (7:00AM(Business hours) to 2:50PM) <BR /><BR />Total : 14:05 hours (6:15 + 7:50)</TD> </TR> <TR> <TD data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;Server&quot;}">Server</TD> <TD data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;INC002&quot;}">INC002</TD> <TD data-sheets-value="{&quot;1&quot;:3,&quot;3&quot;:44903.53125}" data-sheets-numberformat="{&quot;1&quot;:7,&quot;2&quot;:&quot;m/d/yyyy h:mm:ss am/pm&quot;,&quot;3&quot;:1}">12/8/2022 12:45:00 PM</TD> <TD data-sheets-value="{&quot;1&quot;:3,&quot;3&quot;:44908.40625}" data-sheets-numberformat="{&quot;1&quot;:7,&quot;2&quot;:&quot;m/d/yyyy h:mm:ss am/pm&quot;,&quot;3&quot;:1}">12/13/2022 9:45:00 AM</TD> <TD data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;33 hours&quot;}">33 hours</TD> <TD data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;12/8/2022 12:45:00 PM ---&gt; 6:15 hours ( 12:45PM to 7:00 PM (Business hour))\n12/9/2022 ---&gt; 12 hours (Fullday- 7AM to 7PM business hours)\n12/10/2022 ---&gt; 0 (weekend)\n12/11/2022 ---&gt; 0 (weekend)\n12/12/2022 ---&gt; 12 hours (Fullday- 7AM to 7PM business hours)\n12/13/2022 9:45:00 AM ---&gt; 2:45 hours ( 7AM(Business hour) to 9:45AM)\n\nTotal : 33 hours (6:15 + 12 + 0 + 0 + 12 + 2:45)&quot;}">12/8/2022 12:45:00 PM ---&gt; 6:15 hours ( 12:45PM to 7:00 PM (Business hour))<BR />12/9/2022 ---&gt; 12 hours (Fullday- 7AM to 7PM business hours)<BR />12/10/2022 ---&gt; 0 (weekend)<BR />12/11/2022 ---&gt; 0 (weekend)<BR />12/12/2022 ---&gt; 12 hours (Fullday- 7AM to 7PM business hours)<BR />12/13/2022 9:45:00 AM ---&gt; 2:45 hours ( 7AM(Business hour) to 9:45AM)<BR /><BR />Total : 33 hours (6:15 + 12 + 0 + 0 + 12 + 2:45)</TD> </TR> </TBODY> </TABLE> <P>&nbsp;</P> <P>This will tell me that the device Server was unavailable for 47 minutes for that month.</P> <P>&nbsp;</P> <P>Would appreciate any help on this <span class="lia-unicode-emoji" title=":slightly_smiling_face:">🙂</span></P> <P>Regards,</P> <P>Hitha Dhani</P> Fri, 09 Dec 2022 09:04:00 GMT https://community.qlik.com/t5/App-Development/Unavailability-Elapsed-time-Calculation/m-p/2014682#M83845 hitha1512 2022-12-09T09:04:00Z