Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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 | - |
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.
May be load your dates like below, looks like they are not interpreted as date -
Date(Date#(D0,'MM/DD/YYYY'),'YYYY-MM-DD')
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)
May be try like this, this is the set analysis format to compare two fields -
=count( {$<[Open Date]={"=[Open Date]>=D0"}>}Priority)
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?
I am not seeing Open Date in your sample data you have shared... is this coming from another table?
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).
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.
No problem, here is a screen shot of the model related to these two tables. The tables are linked by Affected Software.
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.