Hi there, I am using GoodData Cloud. I am looking for tips on how to calculate the total average of an attribute count (id), and have it display as a line on a graph that is trending by quarters. So far I have only been able to figure out the quarterly (and monthly) average with this metric setup

SELECT AVG({metric/countallactivity_byid})/COUNT({label/METRICDATE.quarter});

However, when I try to add this metric to a line graph, it exactly mirrors the Count of Id line. Suggestions are appreciated. Thank you in advance!

Hi, other approach, that usually works for me is to avoid AVG function and use SUM diveded by COUNT of objects. This way you control how is AVG calculated. Also do you by any chance mean running AVG?

Hi Havala, I’d also suggest to follow Michal’s advice. In case that you are interested in Running Totals, you can check our documentation as well.

Thank you, both!

Can you please break for me how should the calculation work? If you have 5 ids for Q2 count of them is 5, what would be their average?

In the table (first screenshot), the total average = 49 for Q2 '22- Q2 '23. What we want to achieve is a metric that makes a line at 49 (mock-up in second screenshot). Does that answer your question, Michal?

Som metric could be something like this :

SELECT SUM(SELECT {metric/countallactivity_byid} BY {label/METRICDATE.quarter} ALL OTHER))
/ 
(SELECT COUNT({label/METRICDATE.quarter}BY ALL OTHER);

without direct access, I cannot help you more. ;)

Thanks so much, I will give it a go! Appreciate your time 😄