Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ttmaroney
Contributor III
Contributor III

Please help with set analysis around dates I am loading.

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 LiveAffected SoftwareDNeg90D0D14D90All TicketsPrior 90
Go live Name 9Application711/29/20172/27/20183/13/20185/28/201820-
Go live Name 1 Version 2Application19/20/201712/19/20171/2/20183/19/201826-
Go live Name 15Application109/17/201712/16/201712/30/20173/16/2018454-
Go live Name 12 group EApplication99/6/201712/5/201712/19/20173/5/2018152-
Go live Name 12 group DApplication97/12/201710/10/201710/24/20171/8/2018152-
Go live Name 12 group CApplication96/14/20179/12/20179/26/201712/11/2017152-
Go live Name 3Application25/27/20178/25/20179/8/201711/23/20173054-
Go live Name 12 group BApplication95/10/20178/8/20178/22/201711/6/2017152-
Go live Name 12 group AApplication94/12/20177/11/20177/25/201710/9/2017152-
Go live Name 7Application53/31/20176/29/20177/13/20179/27/201745-
Go live Name 1 Version 1Application13/29/20176/27/20177/11/20179/25/201726-
Go live Name 14Application93/15/20176/13/20176/27/20179/11/2017152-
Go live Name 13Application92/15/20175/16/20175/30/20178/14/2017152-
Go live Name 6Application41/29/20174/29/20175/13/20177/28/20176765-
Go live Name 10Application81/24/20174/24/20175/8/20177/23/2017158-
Go live Name 2Application21/7/20174/7/20174/21/20177/6/20173054-
Go live Name 5Application312/31/20163/31/20174/14/20176/29/20171-
Go live Name 4Application211/2/20161/31/20172/14/20175/1/20173054-
Go live Name 11Application89/16/201612/15/201612/29/20163/15/2017158-
Go live Name 8Application67/19/201610/17/201610/31/20161/15/201713174-

 

1 Solution

Accepted Solutions
Digvijay_Singh

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.

View solution in original post

10 Replies
Digvijay_Singh

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

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

ttmaroney
Contributor III
Contributor III
Author

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

Digvijay_Singh

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

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

ttmaroney
Contributor III
Contributor III
Author

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?

sunny_talwar

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

ttmaroney
Contributor III
Contributor III
Author

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).

Digvijay_Singh

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.

ttmaroney
Contributor III
Contributor III
Author

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

Digvijay_Singh

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.