Number of escalations from one group to another

Is there any way to render the name of the previous group at all? We're making a query for which teams our escalations are coming from. If we use "group_id", our query looks like this and is not useful:

But as mentioned above, "group_name" isn't an option, so is there any workaround for this? Another user asked the same question and there was never a response so hoping something's possible, otherwise there's no way to run a report to get the information we want in Explore.

Even if we could even just manually re-label the columns once the query is finished, I would be willing to do that although it seems to be the option to display by name should just be a default attribute. In any case, manually relabelling doesn't seem to be an option so any thoughts would be appreciated. 

Cheers,

@... Indeed there is a workaround for you. You can use the SWITCH & CASE functions to rename each group ID to its name.

You will need to list all groups in your instance within this custom attribute, so it's going to take some maintenance if you add new groups. Here is an example to get you started:

IF (
  [Changes - Field name] = "group_id" AND 
  (
  [Changes - Previous value] != "360012345678" OR
  [Changes - Previous value] != "" OR 
  [Changes - Previous value] != NULL 
  ) AND
  (
  [Changes - New value] = "360012345678" 
  )
) THEN
SWITCH ([Changes - Previous value]) {
CASE "360012345678": "GROUP A NAME"
CASE "360012345679": "GROUP B NAME"
CASE "360012345670": "etc"
    DEFAULT: [Changes - Previous value]
}
ENDIF

Oh snap! Yeah that'll work for me. It's not ideal but it will do just fine for now. Thank you for this!

Hi all, 

will this solution be possible with using a placeholder? 
Currently I would need one calculated metric per group to show the tickets that are escalated to each group. Means instead of using "group_id = "xyz" I would use something like "Ticket group" = [Ticket group]: 

IF ([Changes - Field name] = "Ticket Group" AND [Changes - New value] = [Ticket Group]) THEN
[Ticket ID]
ENDIF

Which would allow building an overview over the tickets escalated into all views. 

Hi Kai,

You might be able to do something where you compare the previous value to the new value. But could you maybe elaborate a bit on what kind of report you want to create? Then I can better guide you in the right direction of how you might be able to create one combined metric instead of multiple.

#helpsome regards,
Marie-Cathrine Sørensen
Developer @ helphouse.io

Hi Marie-Catherine, 

essentially it is quite simple: There are 3 groups- T1, T2 and T3. Escalations go from T1 to T2 to T3. Additionally tickets are created in T1 and T3. Now I would like to see how the workload is in T2 (only escalations into this group) and how the workload is in T3 (Escalations into the group + new tickets). 

I could use the recipe above to build one metric for each group (T1, T2 and T3). More nice would be to have the metric built in a way that allows me to build one query that shows all 3 groups at once. And allows filtering on groups in case we add more groups or only want to show a specific one. 

Main report should be: escalations into a group by month

Hi Kai,

Sorry for the late response. Even though you might have found a solution in the meantime, I would still like to give you my input on how you can solve this issue and create a report along the lines of what you are looking for.

Solution 1

The first solution is to create an attribute instead of a metric. This attribute allows you to divide the Updates metric into different groups (in this case escalations from one group to another). This one is similar to the metric but allows you to combine different scenarios in one. Therefore, this solution also requires you to manually keep it up to date with new groups.

The recipe for this solution would be:

1. Create a new Query by selecting the Ticket updates dataset.
2. In Metrics select Updates.
3. Create a new Standard calculated attribute by clicking the Calculations icon in the panel on the right.
4. Give the attribute a name and past the following into the Formula field:
   

   IF ([Changes - Field name] = "group_id" 
     AND [Changes - Previous value] = "T1 group id"
     AND [Changes - New value] = "T2 group id")
   THEN "T1 -> T2"
   ELIF ([Changes - Field name] = "group_id" 
     AND [Changes - Previous value] = "T2 group id"
     AND [Changes - New value] = "T3 group id")
   THEN "T2 -> T3"
   ENDIF
   

5. Change the values for Previous value and New value for both if statements.
6. Click Save.
7. In Rows select the newly created attribute.
8. Click the attribute and Exclude "NULL".
9. In Columns select Update - Year and Update - Month.
10. Set Visualization type to Table.
11. (Optional) Click the Chart configuration icon > Columns and select Metrics on rows.

You should now have a report similar to this:

The attribute created in this solution can also be used for filtering.

Solution 2

This solution is more dynamic and doesn't require as much maintenance as solution 1, but it might also show escalations that you might not want to see in the report (e.g. escalations to T2 that come from a group that isn't T1).

The recipe for this solution would be:

1. Create a new Query by selecting the Ticket updates dataset.
2. Create a new Standard calculated metric by clicking the Calculations icon in the panel on the right.
3. In Metrics select the newly created metric.
4. Give the metric a name and past the following into the Formula field:
   

   IF ([Changes - Field name] = "group_id" 
     AND [Changes - New value] != [Changes - Previous value]
     AND ([Changes - Previous value] != ""
     OR [Changes - Previous value] != NULL))
   THEN [Ticket ID]
   ENDIF

5. Click Save.
6. In Columns select Update - Year and Update - Month.
7. Set Visualization type to Table.
8. (Optional) Click the Chart configuration icon > Columns and select Metrics on rows.

You should now have a report similar to this:

This one can be filtered using the New value attribute.

#helpsome regards,
Marie-Cathrine Sørensen
Developer @ helphouse.io