topic Re: Please help with set analysis around dates I am loading. in QlikView

Please help with set analysis around dates I am loading.

ttmaroney — Wed, 14 Mar 2018 12:36:54 GMT

I am loading in service tickets with:

[Open Date], [Priority] and [Affected Software].

I am also loading a spreadsheet on go-lives (when systems were added or changed) with:

[Go Live Name],

[Affected Software],

[DNeg90] (The date 90 days before the go-live),

[D0] (Go live date),

[D14] (Date two weeks after go live), and

[D90] (Date 90 days after the go-live).

I want to make a table with go-live name, affected software, and how many service tickets surround that go-live date as follows:

90 days prior
First 14 days
First 90 Days
Prior to go-live date
Since go-live date

I have read many posts and tried like 20 ways, all which failed. Can someone please tell me what I am going wrong?

90 days prior

Tried: =count( {$<[Open Date]={">=$(=Date(D0,'YYYY-MM-DD')"}>}Priority)

First 14 days

Tried: =count({<[Resolve Date]= {">=$(=min(D0))<=$(=max(D14))"}> } Priority)

First 90 Days

Tried: =count({<[Resolve Date]= {">=$(=min(D0))<=$(=max(D90))"}> } Priority)

Prior to go-live date

Tried: =Count({<[Resolve Date]={"<=$(=D0)"}>} Priority)

Since go-live date

Tried:: =count( {$<[Open Date]={">=$(=Date(D0,'YYYY-MM-DD')"}>}Priority)

System - Go Live	Affected Software	DNeg90	D0	D14	D90	All Tickets	Prior 90
Go live Name 9	Application7	11/29/2017	2/27/2018	3/13/2018	5/28/2018	20	-
Go live Name 1 Version 2	Application1	9/20/2017	12/19/2017	1/2/2018	3/19/2018	26	-
Go live Name 15	Application10	9/17/2017	12/16/2017	12/30/2017	3/16/2018	454	-
Go live Name 12 group E	Application9	9/6/2017	12/5/2017	12/19/2017	3/5/2018	152	-
Go live Name 12 group D	Application9	7/12/2017	10/10/2017	10/24/2017	1/8/2018	152	-
Go live Name 12 group C	Application9	6/14/2017	9/12/2017	9/26/2017	12/11/2017	152	-
Go live Name 3	Application2	5/27/2017	8/25/2017	9/8/2017	11/23/2017	3054	-
Go live Name 12 group B	Application9	5/10/2017	8/8/2017	8/22/2017	11/6/2017	152	-
Go live Name 12 group A	Application9	4/12/2017	7/11/2017	7/25/2017	10/9/2017	152	-
Go live Name 7	Application5	3/31/2017	6/29/2017	7/13/2017	9/27/2017	45	-
Go live Name 1 Version 1	Application1	3/29/2017	6/27/2017	7/11/2017	9/25/2017	26	-
Go live Name 14	Application9	3/15/2017	6/13/2017	6/27/2017	9/11/2017	152	-
Go live Name 13	Application9	2/15/2017	5/16/2017	5/30/2017	8/14/2017	152	-
Go live Name 6	Application4	1/29/2017	4/29/2017	5/13/2017	7/28/2017	6765	-
Go live Name 10	Application8	1/24/2017	4/24/2017	5/8/2017	7/23/2017	158	-
Go live Name 2	Application2	1/7/2017	4/7/2017	4/21/2017	7/6/2017	3054	-
Go live Name 5	Application3	12/31/2016	3/31/2017	4/14/2017	6/29/2017	1	-
Go live Name 4	Application2	11/2/2016	1/31/2017	2/14/2017	5/1/2017	3054	-
Go live Name 11	Application8	9/16/2016	12/15/2016	12/29/2016	3/15/2017	158	-
Go live Name 8	Application6	7/19/2016	10/17/2016	10/31/2016	1/15/2017	13174	-

Re: Please help with set analysis around dates I am loading.

Digvijay_Singh — Wed, 14 Mar 2018 15:41:29 GMT

May be load your dates like below, looks like they are not interpreted as date -

Date(Date#(D0,'MM/DD/YYYY'),'YYYY-MM-DD')

Re: Please help with set analysis around dates I am loading.

ttmaroney — Wed, 14 Mar 2018 16:27:33 GMT

Digvijay,

Thanks for the suggestion. All of these are seen as dates. When I add the fields to a sheet they all come in as dates in the same format without modification, see below. I think my problem is in the set analysis expression, when I use the formula below, it works with the upper limit explicitly set. I want to replace that date with D0, but when I do, it fails.

Count({$<[Open Date]={">=01/1/2018"}>}Priority)

Re: Please help with set analysis around dates I am loading.

Digvijay_Singh — Wed, 14 Mar 2018 16:41:01 GMT

May be try like this, this is the set analysis format to compare two fields -

=count( {$<[Open Date]={"=[Open Date]>=D0"}>}Priority)

Re: Please help with set analysis around dates I am loading.

ttmaroney — Wed, 14 Mar 2018 19:01:35 GMT

I was able to use that to get the counts before and after a go-live, but it makes the application very very slow and keeps showing "Not Responding." It get worse when I do two filters, like the one below.

=count( {$< [Open Date]={"=[Open Date]<=D0"},[Open Date]={"=[Open Date]>DNeg90"} >}Priority)

Is there a better way to accomplish this?

Re: Please help with set analysis around dates I am loading.

sunny_talwar — Wed, 14 Mar 2018 19:03:39 GMT

I am not seeing Open Date in your sample data you have shared... is this coming from another table?

Re: Please help with set analysis around dates I am loading.

ttmaroney — Wed, 14 Mar 2018 19:09:33 GMT

Yes, from one source (an SQL database), I'm loading the following on service tickets:

[Open Date], [Priority] and [Affected Software].

From an Excel spreadsheet, I'm loading the following about go-lives:

[Go Live Name],

[Affected Software],

[DNeg90] (The date 90 days before the go-live),

[D0] (Go live date),

[D14] (Date two weeks after go live), and

[D90] (Date 90 days after the go-live).

Re: Please help with set analysis around dates I am loading.

Digvijay_Singh — Wed, 14 Mar 2018 19:11:59 GMT

As Sunny pointed out, you may need to see if tables are associated properly. Can you share image of your data model? Need to see how the table having open date is linking with D0 field table.

Re: Please help with set analysis around dates I am loading.

ttmaroney — Wed, 14 Mar 2018 19:18:09 GMT

No problem, here is a screen shot of the model related to these two tables. The tables are linked by Affected Software.

Re: Please help with set analysis around dates I am loading.

Digvijay_Singh — Wed, 14 Mar 2018 21:41:01 GMT

Not sure where the problem is, Why do we have multiple records showing up for open date in the list box? Is it a timestamp field? You may want to change it to date using floor.

I see many D0 dates for one Affected software? As I understand you are counting Priority for all those records where Open Date <=D0, since you have multiple D0 dates for one affected software, for one record of Tickets table, there will be multile comparisons.

I think comparing Open Date with Max(D0) or Min(D0) may perform better if it suits the overall needs.

Re: Please help with set analysis around dates I am loading.

ttmaroney — Mon, 26 Mar 2018 14:52:34 GMT

Digvijay,

I'm sure your answer was right, and there must be some sort of issue in my model or formatting. This is only something I needed to run once, and since then a coworker was able to easily do it in another tool, which will remain nameless. I'm sure we could get to the bottom of it, but right now, it's not worth the calories since we have our answer. Thanks for the help.