Populating Problem Ticket Subject in Explore

For anyone following along here, I just created this Feedback topic related to this Q&A (per Gab's suggestion), you can see it (and upvote it, if you'd like) here: https://support.zendesk.com/hc/en-us/community/posts/5448109683994-Populating-Problem-Ticket-Subject-in-Explore-

Any updates from Zendesk on this issue? Why do they have a feature in the application that we cannot report properly on? 

No worries - I genuinely appreciate the information you've provided so far, and appreciate you taking the time to share so much of it! 

Kate,
Ah this is where we differ, in our projects.

My project is a once a month report where they want all of the data in a Powerpoint. So I am using explore to get my data, but it's not my final output. So using excel works for me since I am pasting the final table into a power point, not explore. Yes, what I did above is all I've done and it works for me because of the scope of my project. Sorry I can't help more to you. I hope I can help someone else who may be reading this thread though. 

Hi Kelly-Anne 

Thank you again for the detailed explanation - I definitely appreciate your effort! 

Apologies for not making myself clearer - the excel portion makes sense, but my question is how you got this back into Explore without either displaying all problem tickets or manually filtering the incident report on Problem TIcket ID. 

After doing the semi-manual work of creating the xlookup formula/table, how then did you get this to populate in Explore without manually entering the Problem IDs into the Ticket Problem ID?

Perhaps if I provide an overview of what I'm hoping to achieve it will help you understand my dilemma: 

- We track both bugs and Feature Requests as Problem Tickets 
- We are hoping to create a single report which displays the Problem Ticket Subject along with it's incident counts (so essentially it would be a table with columns Problem Ticket ID, Problem Subject, # Incidents) 
- We're currently limited that we can get one table with Problem Ticket ID and Subject (call this chart A), and a second table that shows Problem Ticket ID & # Incidents (call this Chart B) 
- What this requires then is one of two things: we either show all problem tickets on both reports and individuals have to map the IDs between the table with the subject and the table with the incident counts, or we have to manually filter on Problem Ticket ID to only show the subset displayed in the Problem Subject Chart 
- If Chart A is filtered to only show a subset of problem tickets (as we filter out each by the ENG/Product team responsible), then Chart B needs to be manually filtered on Ticket Problem ID to match what's returned in Chart A (I'm aware we could probably get around this manually filtering with additional ticket fields, etc. but that is not an elegant solution and I would prefer not to follow along) 

At this point, I know I can export this and create a chart in Excel, but that data is static and will not update when new incidents are added, or when new problem tickets are created, which means that I have to setup a regular cadence of exporting/recreating the chart in Excel/sharing this out <-- this is what I currently do, and it takes a lot of time. 

What I need is a means for Explore to connect Problem Ticket Subjects w/ Incident counts - pretty basic, and I shouldn't have to go through a million steps to get a report that accurately updates how many incidents are linked to a Problem ticket w/ its subject. I can think of other manual ways to capture this (heck, I can add a numeric field and make my agents update the problem to +1 the vlaue in that field every time a new incident is added, but this again is not ideal, people will get busy and forget, and it shouldn't be the workaround given that Zendesk clearly knows how many incidents there are, just they don't surface that anywhere as an attribute of the Problem ticket for reporting purposes) 

In the screenshot you showed from your first comment - it looks like you're essentially doing the same thing as I am now: creating one report which shows the Problem Ticket ID & the Subject, then the second chart which has the Ticket ID (in this case, the problem ticket) & the incidents count and its up to the viewer of the dashboard to tie the two charts together. This is what I'm hoping to avoid. 

So essentially my question is -- it looks like, in the dashboard in Explore, you're doing one of two things: 
- Displaying all problem tickets on both reports (not a solution we can follow, as again we need to filter the problems & incidents by subset of who is receiving them) 
- Manually filtering on Problem Ticket ID on the incident count report with the Ticket IDs from the Problem Subject report (not a solution, as it requires manual intervention) 

Are you doing something other than one of the two above?

Hi, sorry I am little confused by your question. I am just going to explain how to use xlookup for anyone else who might be reading this. I apologize if this is stuff you already know.

The first table returns Problem Ticket ID, Date Created, Ticket Status, and Ticket Brand, and Problem Ticket Subject

The second table return Ticket Id, and incident counts. 

I export them BOTH tables into excel. Lets say I put them into one page in excel for the sake explaining and it looks like this. 

Then I use xlookup which is an excel function you can think of as a merge or join. I highlighted the part I "do by hand" in yellow. When I say do by hand I mean I write one formula and then apply it to the rest of the column.

Its looking at the rows ticket id, and referencing the Ticket Problem Id and looking for matches. If it matches it returns the number of incidents of that row and it looks like this after you click enter after finishing the formula. 

That formula put that 70 there, I did not do that by hand. Then I just apply that formula to the rest of that yellow column. If you have these tables on different excel worksheets, you can literally just drag the formula down the column, no need to do anything by hand and it auto fills in everything. 

Let me clarify - so, you are manually filtering by ticket problem ID then to build the tables?

The first image is of the two tables in Zendesk Explore I created. I underlined how Problem Ticket ID and Ticket ID were returning the same values. And I can confirm if you look up that ticket number in support those incident counts from the second table and the subject line from the first table do in fact match. 

Hi @Kelly-Anne Richardson

Thank you for the additional information. 

On your first screenshot, is that just an image inserted from your excel table?

I have historically been simply exporting and doing a similar method of generating the tables in excel as needed. However, this has caused issues as essentially I have to re-export on some regular basis since it won't auto-update with new incidents/new problems. 

This places a huge hurdle in the way of seamless and efficient reporting on bugs and feature requests with our Product and Engineering teams) 

I still can't help but feel that Zendesk is really dropping the ball on this one - I know they don't even use Explore internally (data is in Tableau) and it feels like they've just abandoned any improvements to their own tool.