Hi there, I am using GoodData Cloud. I am looking ...
# gd-beginners
h
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!
m
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?
๐๐ผ 1
๐ 2
๐ 1
i
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.
h
Thank you, both!
I appreciate you reviewing, but I can't SUM because it is an attribute. I need the total average of the COUNT, without the date filter
I tried following GD University's Nested Aggregations lesson. I have experimented, using
``BY ALL IN ALL OTHER DIMENSIONS EXCEPT FOR``
and
``WITH PARENT FILTER EXCEPT``
It seems to work OK in a table, but the line looks no different in the insight to the regular COUNT
m
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?
h
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?
m
Som metric could be something like this :
Copy code
``````SELECT SUM(SELECT {metric/countallactivity_byid} BY {label/METRICDATE.quarter} ALL OTHER))
/
(SELECT COUNT({label/METRICDATE.quarter}BY ALL OTHER);``````
๐ 1
๐ 1
h
Thanks so much, I will give it a go! Appreciate your time ๐