Solved: Re: need some help with design... - Qlik Community

QlikviewRaj11 · ‎2020-07-18

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

ImpactMonthStart

ImpactMonthEnd

TypeTicket

190909013708

Procedural deficiencies

Description

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

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

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

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

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

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

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

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

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

QlikviewRaj11 · ‎2020-07-29

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

Kushal_Chawda · ‎2020-07-19

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 · ‎2020-07-20

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 · ‎2020-07-20

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

QlikviewRaj11 · ‎2020-07-20

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

month is filter but only the selected month is displayed

Brett_Bleess · ‎2020-07-29

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 · ‎2020-07-29

Hi,

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

Kushal_Chawda · ‎2020-07-29

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

QlikviewRaj11 · ‎2020-07-29

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,