Hello! Would someone help me with a custom metric please? I'm trying to list the 10 most recent entries but I couldn't managed myself to do that. Since I can't use ORDER BY and LIMIT on MAQL, what do you suggest me to do?

Hi Adair, I tried this out on my end, and I came up with the following: We start by creating a metric using the WHERE TOP IN filter to get us the top 10 Dates. Something like:

SELECT SUM(*Date*) WHERE TOP(10) IN (SELECT SUM(*Date*)

If we use that as our Metric in the Analytical Designer, slicing it by the attribute we want to list, it will show us the Top 10 by most recent date. However, it will have a numerical value which is not quite what we want. The second step is to create a RANK metric for the TOP(10) metric, like so:

SELECT RANK(*TOP 10 dates*) DESC

Finally, create a new Insight in the Analytical Designer, adding the Rank metric above and slicing by your attribute, and you should see the current top 10 most recent entries by Date, ranked from 1 to 10. You can also slice it by Date too, to display the actual dates for each entry. Give that a shot and let me know if it works out for you!

First of all, thanks for your help. Your solution gets me close to my goal. However... this method adds a column ranking the entries by numerical order, but it doesn't limit the report to 10 lines -- which is what I need. Is there a way to make something like that work?

Hmm. Are there more than 10 entries with the same, most recent, date? Because the TOP filter will display tied values, even if this causes it to show more than the 10 intended entries. In that case (and I suspect it is so), have you got any other facts/attributes that can help you break those ties? Like a timestamp, for example.

Yes, there are several entries. What I'm trying to filter is the 10 most recent invoices generated by our ERP. That's for performance following-up purposes.

I’m glad the metric works, but I’m sorry to hear that you weren’t able to add the extra column you’d like. If I may, have you considered maybe adding a Drill Down on the report so that clicking one of the values will lead to a different insight containing the Revenue values you’d like to display? See https://help.gooddata.com/doc/enterprise/en/dashboards-and-insights/dashboards/drilling-in-dashboards/drill-down-in-dashboards/ Let us know if you would need any more help!

Hi, @Adair Júnior if you have one metric that shows top ten using TOP(10) in .... similar to

SELECT SUM(*Date*) WHERE TOP(10) IN (SELECT SUM(*Date*)

. What you can do is, you can include this filter also for revenue metric, which might look like this

SELECT SUM(*Revenue*) WHERE TOP(10) IN (SELECT SUM(*Date*)

, basically you can use that TOP(10) IN METRIC filter also in different metrics. Hope this helps.