Hi, I would like to do metric of SUM of revenue fo...
# gooddata-cloud
m
Hi, I would like to do metric of SUM of revenue for all advertisers minus top 10 advertisers - so I could have it as metric in graf - like top 10 + others. DOes anybody know how to do it best? I tried this: SELECT SUM({metric/revenue_flat_rate_impressions}) WHERE TOP(10) IN (SELECT {metric/revenue_flat_rate_impressions}) - but it just select top 10 correctly when sliced, but how to do it properly?
f
Hi Markéta! I tested on my end, and this can be done by using the NOT logical operator. Like so:
Copy code
``````SELECT SUM({metric/revenue_flat_rate_impressions})
WHERE NOT TOP(10) IN
(SELECT {metric/revenue_flat_rate_impressions})``````
This should properly display everything but the Top 10 in the selected category.
❤️ 1
m
Hi Francisco, thank you, this helped, but now I am facing one more issue, if I put next to it something to slice it by - for example name of advertiser, I can see that there is no value for first top 10 which is exactly what I searched from, but if I remove this slicing element, there is no sum of the values.
I figure it out, the solution is: SELECT SUM({metric/revenue_flat_rate_impressions}) WHERE NOT TOP(10) IN (SELECT {metric/revenue_flat_rate_impressions} BY {label/advertisers.name} WITHOUT PARENT FILTER ) 🙂 Thx!
🙌 1
Maybe one more question, now I managed to do a graf of TOP 10 vs. OTHERS, but what if I would like to have these TOP10 specific by names, but OTHERS all together, does exist some way for cases like this?
f
Hi Markéta, I’m afraid that it can’t be done for this kind of use-case - an insight that compares the Top-10 with the rest, but listing each option in the Top 10. It would be necessary to have separate metrics for each of the top 10 advertisers, which I’m not sure is possible. If I may suggest, how about using a Drill-in interaction instead? You can configure it so that when a user clicks the section of the chart for the Top 10 it leads them to another visualization specifying the actual names in the section.
m