Recent searches


No recent searches

Explore recipe: Reporting on nested drop-down fields



image avatar

Rob Stack

Zendesk Documentation Team

Edited Jun 21, 2024


8

22

22 comments

Hi there, my brain my just be foggy but what if we have 4 "levels"? What would category 3 prep look like? I cant seem to sort out in the attribute why its pulling level 2 vs level 3 etc. Thanks.

John

0


Great tutorial @..., thank you so much.

I would like to ask the same question as @..., what would the attributes look like for a 4th level? I've tried and I just can't work the same magic!

Your help would be greatly appreciated.

0


image avatar

Rob Stack

Zendesk Documentation Team

Hi John Burns and Tom Montgomery, I had to do a little research to get the answer to this so apologies for the delay. Here's what I received back. I hope this helps!

LEVEL 3 PREP:
IF (CONTAINS([Level 2 Prep],"::")) THEN
SUBSTR([Level 2 Prep],
FIND([Level 2 Prep],"::",0)+2,
LENGTH([Level 2 Prep]))
ELSE "" ENDIF 

LEVEL 3:
IF (CONTAINS([Level 3 Prep],"::")) THEN
LEFTPART([Level 3 Prep], FIND([Level 3 Prep],"::",0) )
ELSE [Level 3 Prep] ENDIF

LEVEL 4:
IF (CONTAINS([Level 3 Prep],"::")) THEN
SUBSTR([Level 3 Prep],FIND([Level 3 Prep],"::",0)+2,LENGTH([Level 3 Prep]))
ELSE "" ENDIF

1


Hey Team,

@...

Is there anyways we could report on just the level 3? Or would you recommend just hiding those lower levels in the chart configuration?

And could I potentially layer this with: https://support.zendesk.com/hc/en-us/articles/360052540974-Explore-recipe-Excluding-tickets-closed-by-merge 

Thanks!

 

0


@...

If I understand what you need correctly, it's actually pretty simple. Once you have setup all the levels above, just add "[name of level 3]" to the column or row and it will only show values that come under that category.

I hope that helps!

0


Thanks @...!

Essentially we want to just show the level 3, as its the "meat and potatoes" of our reporting. 

Im curious if you'd know, could we modify the initial metric to just show solved tickets and exclude the "closed_by_merge" tag?

We want to use this as a way to track agents solved tickets, and I think a hybrid of this post and this one would do a great job. 

Thanks!

0


Hey Theodore,

We do exactly that - we exclude the tag. It works like a charm.

I set this up almost a year ago and it took under an hour from what I remember. It's definitely worth doing.

To exclude the tag, open up your query and under "filters" add ticket tags then type closed_by_merge in the search bar. Make sure "Excluded" is selected.

Then, you can add "Level 3" in the Columns section and it will look something like this:

Note: in this example I also filtered by brand to reduce the sample size.

I hope that helps!

1


Thanks @...!

 

I've tried using this setup in the past, but after spot checking a few tickets, it would still include some that had the tag. Did you do any custom metrics on this query?

0


No problem @...,

I have noticed that too. We ignore that issue as the data gives us a good overview - a few additional merge tickets is not a huge problem to us. The solution in the article you shared above might be your best bet.

Maybe one of the Zendesk team can give some more insight into why the exclude tag feature is not 100% reliable.

0


image avatar

Rob Stack

Zendesk Documentation Team

Hi Tom Montgomery, I wasn't completely sure so I've opened a ticket so someone on the team can investigate. Hopefully, you'll here from someone soon.

0


Let's say I have three level 1 categories, website help, information update and Technical issue.

  • Website help::Navigation
  • Website help::login
  • Website help::signup
  • Information update::Name
  • Information update::dob
  • Information update::Address
  • Technical Issue::Payment
  • Technical Issue::Order

How would I roll up level 2s and level threes into the 3 main categories? Like the below

0


Hi there - 

Related question here, what if you want to report on the aggregate of your top level categories and the first category is not a constant?  For example, we have a Product Area field that has nesting implemented that looks like:

Product 1:: Sub Product :: Sub Sub Product (so on and so forth)

Product 1 :: Sub Product 2 :: Sub Sub Product 2

Product 2:: Sub Product :: Sub Sub Product

I want to be able to pull aggregated ticket volume for all the top level categories.  The formula in this article presumes the top level category is the same for all entries - but in our case it is not.  Any advise or solution is appreciated.

0


image avatar

Gab Guinto

Zendesk Customer Care

Hello,
 
The formula described above should allow you to report on top level items, even if you have multiple top level categories. Here's a sample set of nested dropdown field values and how the data will look like in Explore.


0


Hi,

My custom field name is Category with by 5 categories, sub and sub-sub. I followed the formula:

IF (CONTAINS([Category],"::")) THEN
LEFTPART([Category],FIND([Category],"::",0)) 
ELSE [Category] ENDIF

But nothing appears only what looks like old tags. Any idea what else I can try?

1


image avatar

Gab Guinto

Zendesk Customer Care

Hi Vera,

We may need to take a look at the report saved in your Explore. Let me create a ticket for you. 

0


Hi Gab,

I am trying to extract first level and second level and nothing appears in the report for me too.

0


image avatar

Gab Guinto

Zendesk Customer Care

Hi Ankit,

It may be worth checking if you have another ticket or user field with an identical name. You can check out this guide: My custom ticket field attribute is not returning any results.

Let us know if this resolved the issue, or you can get in touch with the Customer Care team if you need further help. Thanks!

0


Hello, is there a video on this or a deeper explanation?

I have a gigantic tree that has over 200+ combinations that are all connected through conditional formatting. 

The tree is the contact reason a customer is contacting our support team about. 

Essentially there's 1 field that breaks into 10 smaller fields, and each of those smaller fields have sub fields that can go 6 levels deep, but not all go that far, some of them just 3 or 4. 

Is the top most field Level 1 and the lowest field level 6?

I'd like to set up a report that is similiar to a pivot table in Excel where each level rolls up to a larger level which rolls up to a larger level and finally up to the top most level

0


We have 7 levels nestedloop dropdown field. And got this error:

Calculations are too complex. Simplify calculated metrics and attributes or remove some of them. <a href=https://support.zendesk.com/hc/en-us/articles/5133326635162>Learn about Explore limits</a> (Error code: 5f863b30)

We got this error on level 5 nested loop and the further:

1. Level 5

2. Level 6 (Prep)

3. Level 6

4. Level 7 (Prep)

5. Level 7

 

Is there another formula to handle this error?

0


image avatar

Alex Zheng

Zendesk Customer Care

Hey Saasten,

It looks like you opened up a ticket about this issue, someone will respond to you there and continue to assist.

0


Hello all,

Maybe some of you have the same issue as me, namely having more than 3 levels in the nested loop field, and want to do reporting from the nested loop field but are limited by Zendesk which can only pull 3 levels from the nested loop field in the report (based on this article).

Below I have the latest formula regarding nested loops for level 1 up to whatever level you've created (without get Zendesk limitation "calculations are too complex...")

Below is the formula

Level    Formula
1    REGEXP_EXTRACT([Category Detail], "([^:]*)")
2    REGEXP_EXTRACT([Category Detail], "(?<=^[^:]*::)([^:]*)")
3    REGEXP_EXTRACT([Category Detail], "(?<=^[^:]*::[^:]*::)([^:]*)")
4    REGEXP_EXTRACT([Category Detail], "(?<=^[^:]*::[^:]*::[^:]*::)([^:]*)")
5    REGEXP_EXTRACT([Category Detail], "(?<=^[^:]*::[^:]*::[^:]*::[^:]*::)([^:]*)")
6    REGEXP_EXTRACT([Category Detail], "(?<=^[^:]*::[^:]*::[^:]*::[^:]*::[^:]*::)([^:]*)")
7    REGEXP_EXTRACT([Category Detail], "(?<=^[^:]*::[^:]*::[^:]*::[^:]*::[^:]*::[^:]*::)([^:]*)")


you need to change [Category Detail] to your nested loop field

With this new formula you no longer need to create preparation attributes

 

hope this help, guys.

1


Please sign in to leave a comment.