Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
QlikviewRaj11
Contributor III
Contributor III

need some help with design...

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

IncidentNumberCauseCodeDescriptionHostnameIdImpactAppNameAPPIDACCTLOBOutageMinSeverityTimeTicketStatusOpenTimeEasternImpactStartImpactStartImpactMonthStartImpactMonthEndTypeTicket
190909013708Procedural deficienciesDescriptionNULLNULLNULLAppName103144CCBU10289/9/19 6:12 PMClosed9/9/19 12:22 PM9/8/19 8:00 AM9/8/20199/1/20199/30/2019Accountable
191113012075Application Software NULLNULLImpact DescAppName1103299CCBU411/13/19 3:04 PMClosed11/13/19 10:03 AM11/13/19 1:41 PM11/13/201911/1/201911/30/2019Accountable
191121018058Application Software NULLNULLImpact DescAppName1103299CCBU8111/21/19 8:30 PMClosed11/21/19 3:20 PM11/21/19 8:20 PM11/21/201911/1/201911/30/2019Accountable
191130000983Application Software HostName11NULLImpact DescAppName1103299CCBU18011/30/19 2:30 AMClosed11/29/19 8:55 PM11/30/19 12:45 AM11/30/201911/1/201911/30/2019Accountable
191130005087Application Software HostName12NULLImpact DescAppName1103299CCBU2011/30/19 1:22 PMClosed11/30/19 4:04 AM11/30/19 8:48 AM11/30/201911/1/201911/30/2019Accountable
191201001832Application Software NULLNULLImpact DescAppName1103299CCBU12012/1/19 2:44 AMClosed11/30/19 9:44 PM11/30/19 1:42 PM11/30/201911/1/201911/30/2019Accountable
191210009117Design HostName13NULLImpact DescAppName1103299CCBU312/10/19 10:53 AMClosed12/10/19 5:53 AM12/10/19 10:46 AM12/10/201912/1/201912/31/2019Accountable
191213015718Application Software NULLNULLImpact DescAppName1103299CCBU23312/13/19 6:19 PMClosed12/13/19 1:19 PM12/13/19 1:17 PM12/13/201912/1/201912/31/2019Accountable
190624020123Application Software HostName13NULLImpact DescAppName1103299CCBU1116/24/19 10:15 PMClosed6/24/19 6:10 PM6/24/19 8:36 PM6/24/20196/1/20196/30/2019Accountable
200404009298Maintenance NOT FOUNDNULLImpact DescAppName281717CCBU1154/4/20 6:39 PMClosed4/4/20 2:01 PM4/4/20 5:42 PM4/4/20204/1/20204/30/2020Impacted
190804003654Change Mgt Hostname14NULLImpact DescAppName385980CCBU18/4/19 4:42 AMClosed8/4/19 12:42 AM8/4/19 4:25 AM8/4/20198/1/20198/31/2019Impacted
200404009298Maintenance NOT FOUNDNULLImpact DescAppName385980CCBU1154/4/20 6:39 PMClosed4/4/20 2:01 PM4/4/20 5:42 PM4/4/20204/1/20204/30/2020Impacted
191013014492Procedural deficiencies HostName15NULLImpact DescAppName385980CCBU7710/13/19 7:01 PMClosed10/13/19 2:50 PM10/13/19 5:56 PM10/13/201910/1/201910/31/2019Impacted
191102009544Maintenance HostName16NULLImpact DescAppName481717CCBU28611/2/19 4:03 PMClosed11/2/19 11:37 AM11/2/19 2:44 PM11/2/201911/1/201911/30/2019Impacted
191102012455Maintenance HostName17NULLImpact DescAppName481717CCBU41211/3/19 9:53 AMClosed11/2/19 5:45 PM11/2/19 8:34 PM11/2/201911/1/201911/30/2019Impacted
200119010784Application Software NULLNULLImpact DescAppName481717CCBU6601/19/20 9:01 PMClosed1/19/20 4:01 PM1/19/20 1:31 PM1/19/20201/1/20201/31/2020Impacted
200119010784Application Software NULLNULLImpact DescAppName585980CCBU6601/19/20 9:01 PMClosed1/19/20 4:01 PM1/19/20 1:31 PM1/19/20201/1/20201/31/2020Impacted
200628002348Maintenance NULLNULLImpact DescAppName281717CCBU46/28/20 3:30 AMClosed6/27/20 11:30 PM6/28/20 1:23 AM6/28/20206/1/20206/30/2020Impacted
200628002348Maintenance NULLNULLImpact DescAppName385980CCBU46/28/20 3:30 AMClosed6/27/20 11:30 PM6/28/20 1:23 AM6/28/20206/1/20206/30/2020Impacted
200118001631Procedural deficiencies HostName19NULLImpact DescAppName324380CCBU451/18/20 2:54 AMClosed1/17/20 9:54 PM1/18/20 2:34 AM1/18/20201/1/20201/31/2020Impacted
190616011730Asset / Configuration HostName19NULLImpact DescAppName385980CCBU746/16/19 12:02 PMClosed6/16/19 8:02 AM6/16/19 12:01 PM6/16/20196/1/20196/30/2019Impacted
190707002497Application Software HostName19NULLImpact DescAppName385980CCBU17/7/19 2:56 AMClosed7/6/19 10:56 PM7/7/19 2:33 AM7/7/20197/1/20197/31/2019Impacted
190616003639Application Software HostName19NULLImpact DescAppName385980CCBU117.36/16/19 3:24 AMClosed6/15/19 11:24 PM6/16/19 3:24 AM6/16/20196/1/20196/30/2019Impacted
191008001333Validation and Testing HostName19NULLImpact DescAppName385980CCBU110/8/19 2:04 AMClosed10/7/19 9:14 PM10/8/19 1:02 AM10/8/201910/1/201910/31/2019Impacted
191109001896Application Software HostName19NULLImpact DescAppName385980CCBU111/9/19 2:59 AMClosed11/8/19 9:59 PM11/9/19 2:36 AM11/9/201911/1/201911/30/2019Impacted
200410000600Change Mgt HostName19NULLImpact DescAppName385980CCBU14/14/20 3:39 PMClosed4/9/20 8:17 PM4/9/20 11:41 PM4/9/20204/1/20204/30/2020Impacted

 

AOSIDApplicationNameAppOwnerCTOCTOSIDLOB_TempWeeklySLAAPPIDSUBLOBTarget_TempTGOSNAME
E123AppNameAppOwnerNameCTONameCIDLOBNAME167.88103144SUBLOBNAME99.8%TGOSNAME
F123AppName3AppOwnerNameCTONameCIDLOBNAME167.8885980SUBLOBNAME99.8%TGOSNAME1
J123AppName2AppOwnerNameCTONameCIDLOBNAME167.8881717SUBLOBNAME99.8%TGOSNAME2
R123AppName1AppOwnerNameCTONameCIDLOBNAME167.88103299SUBLOBNAME99.8%

TGOSNAME3

 

T123AppNameT123AppOwnerNameCTONameCIDLOBNAME167.88123123SUBLOBNAME99.8%

TGOSNAME4

T456AppNameT456AppOwnerNameCTONAMECIDLOBNAME167.88456456SUBLOBNAME99.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

 

1 Solution

Accepted Solutions
QlikviewRaj11
Contributor III
Contributor III
Author

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,

 

View solution in original post

8 Replies
Kushal_Chawda

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?

QlikviewRaj11
Contributor III
Contributor III
Author

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

 

QlikviewRaj11
Contributor III
Contributor III
Author

Hi Kush, I added 2 records in dataset2 which do not exist in dataset1 - thanks again for your time

QlikviewRaj11
Contributor III
Contributor III
Author

Hi Kush, thanks for your help, got stuck..

month is filter but only the selected month is displayed

Brett_Bleess
Former Employee
Former Employee

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
QlikviewRaj11
Contributor III
Contributor III
Author

Hi,

Thank you. I figured out the solution for this one. Thanks a lot for checking

 

Kushal_Chawda

@QlikviewRaj11  can you shortly describe the solution so that it will be useful for others  to refer

QlikviewRaj11
Contributor III
Contributor III
Author

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,