Return Article Title in 1 language, roll up of all Language views?

Hi 420860762414, thanks so much for sharing this—this is really helpful! I've gone ahead and created an Explore recipe using your instructions to help more people find this info in the future: Explore recipe: Reporting on article views for all languages.

Thanks again!

That's awesome CJ! 👏👏👏

I actually cracked this! Bear with me, because it’s going to be a bit of a long post.

You will need to build 1 custom metric, and 2 custom attributes.

First, lets’s build a fixed metric, that fixes the SUM of Views, to the Article ID. This means that no other attribute can slice the total views.

Metric:  Views, All Locales Roll-Up
ATTRIBUTE_FIX(SUM(Views), [Article ID])

Next, we’re going to build an underlying attribute. This looks at

Attribute 1: Location plus Article ID
IF [Article ID] != "" THEN 

[Article ID] + [Article locale]

ENDIF

What this does, is create an attribute that is the Article ID, plus locale, so that we can use it to choose a single language to return, without using filters on the query.

Now that it’s built, we’ll use it to return only the Article title for EN-US for a given ID: 

Attribute 2: EN Article Title
IF 

(CONTAINS(LOWERCASE([Location plus Article ID]),"en-us"))

THEN 

[Article title]

ENDIF 

Then, put the metric as your main metric, and the attribute as your main attribute. Set the date range for when the views happen, and there you have it. You should have a list of article titles in English, with the views for the article for *all* language views, rolled up as the total.

If you place the article id row above the article title, and create a sum for that, I believe you should be able to get there roll up power id. You would still see multiple titles though.

Hi 1264091921889, thanks but your example shows the title in every language still. I want to see only one title, with view count showing the full count from all languages. The article ID should not repeat. If I add in a filter for the article language, it *does* affect the totals, so I still wasn't able to get to my desired end result. 

1203775278, likewise, thanks, but I actually do need the Article Title,  I just want it to show ONLY the title in one language, with a roll up of views for all languages. Yours would give me a roll up without the title in a single language, which is what I am posting looking for a solution for. 

Hi 420860762414, you should also be able to do this using the following steps:

1. Create a new query in the Guide > Knowledge Base dataset.
2. Create a standard calculated metric with the following formula: ATTRIBUTE_FIX(SUM(Views), [Article ID]) 
This metric will return all the views for a given article ID regardless of language.
3. In the Metrics panel, add the metric you just created using the SUM aggregator.
4. In the Rows panel, add the Article ID and Article title attributes.
5. In the Columns panel, add the Article translation language attribute.
6. Change the Visualization type to Table.
7. In the English (United States) column, click the name of the metric you created in step 2. This should sort the column so that all rows with values appear at the top, and all empty rows appear at the bottom. (Despite the "English" heading, the values in this column represent the views for all languages together, based on the standard metric you created. Sorting the column this way just helps you get a list of human-readable article titles in your preferred language, rather than just the article IDs.)
8. Optionally, you can hide the columns for other languages to make the table less cluttered: Go to Result manipulation > Hide part of your result and use the Hide last X columns (depending on how many other languages you have in your knowledge base).

You should end up with something that looks like this:

Hi CJ,

If you clone the "Zendesk Guide: Knowledge base" dashboard, and look for the query called "Knowledge Base: Article table", you should be able to modify this to your need.

I don't currently have multilingual content in my help center, so I haven't reality tested this, but I'm fairly sure it should work.

Save a new version of "Knowledge Base: Article table" and in the rows section, look for an attribute called "Article ID" and add it - the ID should be the same for different language versions of the same article, you should be able to verify this by comparing "Article ID" and "Article Titles".

You can remove Article Title and other elements you don't need.

I hope this helps you out.