Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All, could someone please help with this kind of scenario...
1)Dataset1: I have the incident data which includes the application details when there is an outage. The metric it includes is outagemin which is the total number of mins it is outage for that date. The date field ImpactStart is the date on which the outage happened so every application will not have the outage every day so there may not be an application at all in the last 13 months which never failed so no outage.. for this application the compliance% is 100%
2).Dataset2: This dataset is like reference data which includes the application details like name, LOB, Sub Lob, Target, SLA. The SLA given is for week so I convert it into monthly weeklySLA/7*total number of days in that month
IncidentNumber | CauseCode | Description | Hostname | Id | Impact | AppName | APPID | ACCTLOB | OutageMin | SeverityTime | TicketStatus | OpenTimeEastern | ImpactStart | ImpactStart | ImpactMonthStart | ImpactMonthEnd | TypeTicket |
190909013708 | Procedural deficiencies | Description | NULL | NULL | NULL | AppName | 103144 | CCBU | 1028 | 9/9/19 6:12 PM | Closed | 9/9/19 12:22 PM | 9/8/19 8:00 AM | 9/8/2019 | 9/1/2019 | 9/30/2019 | Accountable |
191113012075 | Application Software | NULL | NULL | Impact Desc | AppName1 | 103299 | CCBU | 4 | 11/13/19 3:04 PM | Closed | 11/13/19 10:03 AM | 11/13/19 1:41 PM | 11/13/2019 | 11/1/2019 | 11/30/2019 | Accountable | |
191121018058 | Application Software | NULL | NULL | Impact Desc | AppName1 | 103299 | CCBU | 81 | 11/21/19 8:30 PM | Closed | 11/21/19 3:20 PM | 11/21/19 8:20 PM | 11/21/2019 | 11/1/2019 | 11/30/2019 | Accountable | |
191130000983 | Application Software | HostName11 | NULL | Impact Desc | AppName1 | 103299 | CCBU | 180 | 11/30/19 2:30 AM | Closed | 11/29/19 8:55 PM | 11/30/19 12:45 AM | 11/30/2019 | 11/1/2019 | 11/30/2019 | Accountable | |
191130005087 | Application Software | HostName12 | NULL | Impact Desc | AppName1 | 103299 | CCBU | 20 | 11/30/19 1:22 PM | Closed | 11/30/19 4:04 AM | 11/30/19 8:48 AM | 11/30/2019 | 11/1/2019 | 11/30/2019 | Accountable | |
191201001832 | Application Software | NULL | NULL | Impact Desc | AppName1 | 103299 | CCBU | 120 | 12/1/19 2:44 AM | Closed | 11/30/19 9:44 PM | 11/30/19 1:42 PM | 11/30/2019 | 11/1/2019 | 11/30/2019 | Accountable | |
191210009117 | Design | HostName13 | NULL | Impact Desc | AppName1 | 103299 | CCBU | 3 | 12/10/19 10:53 AM | Closed | 12/10/19 5:53 AM | 12/10/19 10:46 AM | 12/10/2019 | 12/1/2019 | 12/31/2019 | Accountable | |
191213015718 | Application Software | NULL | NULL | Impact Desc | AppName1 | 103299 | CCBU | 233 | 12/13/19 6:19 PM | Closed | 12/13/19 1:19 PM | 12/13/19 1:17 PM | 12/13/2019 | 12/1/2019 | 12/31/2019 | Accountable | |
190624020123 | Application Software | HostName13 | NULL | Impact Desc | AppName1 | 103299 | CCBU | 111 | 6/24/19 10:15 PM | Closed | 6/24/19 6:10 PM | 6/24/19 8:36 PM | 6/24/2019 | 6/1/2019 | 6/30/2019 | Accountable | |
200404009298 | Maintenance | NOT FOUND | NULL | Impact Desc | AppName2 | 81717 | CCBU | 115 | 4/4/20 6:39 PM | Closed | 4/4/20 2:01 PM | 4/4/20 5:42 PM | 4/4/2020 | 4/1/2020 | 4/30/2020 | Impacted | |
190804003654 | Change Mgt | Hostname14 | NULL | Impact Desc | AppName3 | 85980 | CCBU | 1 | 8/4/19 4:42 AM | Closed | 8/4/19 12:42 AM | 8/4/19 4:25 AM | 8/4/2019 | 8/1/2019 | 8/31/2019 | Impacted | |
200404009298 | Maintenance | NOT FOUND | NULL | Impact Desc | AppName3 | 85980 | CCBU | 115 | 4/4/20 6:39 PM | Closed | 4/4/20 2:01 PM | 4/4/20 5:42 PM | 4/4/2020 | 4/1/2020 | 4/30/2020 | Impacted | |
191013014492 | Procedural deficiencies | HostName15 | NULL | Impact Desc | AppName3 | 85980 | CCBU | 77 | 10/13/19 7:01 PM | Closed | 10/13/19 2:50 PM | 10/13/19 5:56 PM | 10/13/2019 | 10/1/2019 | 10/31/2019 | Impacted | |
191102009544 | Maintenance | HostName16 | NULL | Impact Desc | AppName4 | 81717 | CCBU | 286 | 11/2/19 4:03 PM | Closed | 11/2/19 11:37 AM | 11/2/19 2:44 PM | 11/2/2019 | 11/1/2019 | 11/30/2019 | Impacted | |
191102012455 | Maintenance | HostName17 | NULL | Impact Desc | AppName4 | 81717 | CCBU | 412 | 11/3/19 9:53 AM | Closed | 11/2/19 5:45 PM | 11/2/19 8:34 PM | 11/2/2019 | 11/1/2019 | 11/30/2019 | Impacted | |
200119010784 | Application Software | NULL | NULL | Impact Desc | AppName4 | 81717 | CCBU | 660 | 1/19/20 9:01 PM | Closed | 1/19/20 4:01 PM | 1/19/20 1:31 PM | 1/19/2020 | 1/1/2020 | 1/31/2020 | Impacted | |
200119010784 | Application Software | NULL | NULL | Impact Desc | AppName5 | 85980 | CCBU | 660 | 1/19/20 9:01 PM | Closed | 1/19/20 4:01 PM | 1/19/20 1:31 PM | 1/19/2020 | 1/1/2020 | 1/31/2020 | Impacted | |
200628002348 | Maintenance | NULL | NULL | Impact Desc | AppName2 | 81717 | CCBU | 4 | 6/28/20 3:30 AM | Closed | 6/27/20 11:30 PM | 6/28/20 1:23 AM | 6/28/2020 | 6/1/2020 | 6/30/2020 | Impacted | |
200628002348 | Maintenance | NULL | NULL | Impact Desc | AppName3 | 85980 | CCBU | 4 | 6/28/20 3:30 AM | Closed | 6/27/20 11:30 PM | 6/28/20 1:23 AM | 6/28/2020 | 6/1/2020 | 6/30/2020 | Impacted | |
200118001631 | Procedural deficiencies | HostName19 | NULL | Impact Desc | AppName3 | 24380 | CCBU | 45 | 1/18/20 2:54 AM | Closed | 1/17/20 9:54 PM | 1/18/20 2:34 AM | 1/18/2020 | 1/1/2020 | 1/31/2020 | Impacted | |
190616011730 | Asset / Configuration | HostName19 | NULL | Impact Desc | AppName3 | 85980 | CCBU | 74 | 6/16/19 12:02 PM | Closed | 6/16/19 8:02 AM | 6/16/19 12:01 PM | 6/16/2019 | 6/1/2019 | 6/30/2019 | Impacted | |
190707002497 | Application Software | HostName19 | NULL | Impact Desc | AppName3 | 85980 | CCBU | 1 | 7/7/19 2:56 AM | Closed | 7/6/19 10:56 PM | 7/7/19 2:33 AM | 7/7/2019 | 7/1/2019 | 7/31/2019 | Impacted | |
190616003639 | Application Software | HostName19 | NULL | Impact Desc | AppName3 | 85980 | CCBU | 117.3 | 6/16/19 3:24 AM | Closed | 6/15/19 11:24 PM | 6/16/19 3:24 AM | 6/16/2019 | 6/1/2019 | 6/30/2019 | Impacted | |
191008001333 | Validation and Testing | HostName19 | NULL | Impact Desc | AppName3 | 85980 | CCBU | 1 | 10/8/19 2:04 AM | Closed | 10/7/19 9:14 PM | 10/8/19 1:02 AM | 10/8/2019 | 10/1/2019 | 10/31/2019 | Impacted | |
191109001896 | Application Software | HostName19 | NULL | Impact Desc | AppName3 | 85980 | CCBU | 1 | 11/9/19 2:59 AM | Closed | 11/8/19 9:59 PM | 11/9/19 2:36 AM | 11/9/2019 | 11/1/2019 | 11/30/2019 | Impacted | |
200410000600 | Change Mgt | HostName19 | NULL | Impact Desc | AppName3 | 85980 | CCBU | 1 | 4/14/20 3:39 PM | Closed | 4/9/20 8:17 PM | 4/9/20 11:41 PM | 4/9/2020 | 4/1/2020 | 4/30/2020 | Impacted |
AOSID | ApplicationName | AppOwner | CTO | CTOSID | LOB_Temp | WeeklySLA | APPID | SUBLOB | Target_Temp | TGOSNAME |
E123 | AppName | AppOwnerName | CTOName | CID | LOBNAME | 167.88 | 103144 | SUBLOBNAME | 99.8% | TGOSNAME |
F123 | AppName3 | AppOwnerName | CTOName | CID | LOBNAME | 167.88 | 85980 | SUBLOBNAME | 99.8% | TGOSNAME1 |
J123 | AppName2 | AppOwnerName | CTOName | CID | LOBNAME | 167.88 | 81717 | SUBLOBNAME | 99.8% | TGOSNAME2 |
R123 | AppName1 | AppOwnerName | CTOName | CID | LOBNAME | 167.88 | 103299 | SUBLOBNAME | 99.8% | TGOSNAME3
|
T123 | AppNameT123 | AppOwnerName | CTOName | CID | LOBNAME | 167.88 | 123123 | SUBLOBNAME | 99.8% | TGOSNAME4 |
T456 | AppNameT456 | AppOwnerName | CTONAME | CID | LOBNAME | 167.88 | 456456 | SUBLOBNAME | 99.8% | TGOSNAME5 |
Note1:The outage is SUM for the APPID from the dataset1 - I cannot use the Month from ImpactStart to aggregate and use the Month from ImpactStart in the Pivot because the ImPact Month may not be available when there is no outage data
Note2: WeeklySLA in the dataset I need to convert it into monthly by dividing by 7 and multiplied/times number of days in that month
Pivot Table: for the last 13 months - this is where I cannot use the month extracted from ImpactStart because the data may be not available for an APPID for all 13 months also
LOB Last 13Month Last 12Month Last11Month Last10.....Last previous month (for ex: June 2020)
PivotTable2: at APPID level
APPID APPNAME LOB AppOwner Last13Month Last12Month Last11Month ....Last Previous Month (For ex: June 2020)
Compliance% = sum(Outage) group by APPID
calculate the % between 1 and 0 - If an APP has no outage then show compliance% as 100%
Could someone pls help....I used the last13 months from the ImpactStart but this is not working when I select the filters I do not see all 13 months..i hope this is clear
Hi Kush,
I have to use the calendar table to get the last 13 months then I use the distinct APPID query - Cartesian the calendar with the distinct APPID - then join this dataset to the dataset where I get my metric based on the APPID and the reporting date
Thanks for your time,
If you don't have outage for particular month in last 13 the would you want to show 92% compliance? Also do you want to show that Month with 0 outage in pivot table if there is no outage for that month? Do you want the last 13 months dynamic for eg. user selects Mar 2019 so you need to display form Jan 2018 to Mar 2019?
Hi Kush, Thank you,
1).If there is no outage at all for that month then the compliance% for that APPID is 100% because it is available for all days in that month
2).I do not need to show the outage and MonthlySLA in the pivot table they are just need to calculate the Compliance%
when there is no outage use the 0.0001 as outage and when there is no MonthlySLA use 693.78 - This will give us some number which can be converted into compliance 100% - If the Outage/MonthlySLA is <0 then 100%
3) my dataset includes 13 months data from the database.. I filter at the DB level for 13 months so I am good
in the pivot table I show or display 13 months... we don't have the month selection we have other filters like LOB, SUB LOB -- month is always constatnt
Hi Kush, I added 2 records in dataset2 which do not exist in dataset1 - thanks again for your time
Hi Kush, thanks for your help, got stuck..
month is filter but only the selected month is displayed
Just adding a new comment to kick this back up in the list in case someone else has time to have a look at things.
Regards,
Brett
Hi,
Thank you. I figured out the solution for this one. Thanks a lot for checking
@QlikviewRaj11 can you shortly describe the solution so that it will be useful for others to refer
Hi Kush,
I have to use the calendar table to get the last 13 months then I use the distinct APPID query - Cartesian the calendar with the distinct APPID - then join this dataset to the dataset where I get my metric based on the APPID and the reporting date
Thanks for your time,