I am hoping to get a view of the average over time...
# gd-beginnersh
Hesseltje van Goor
10/10/2022, 2:11 PMI am hoping to get a view of the average over time, but so far the COUNT MAQL has been throwing me for a loop
Hesseltje van Goor
10/10/2022, 2:12 PMOur data model looks like this:
Hesseltje van Goor
10/10/2022, 2:13 PMwhich I believe matches what Jacques describes here: https://community.gooddata.com/metrics-and-maql-kb-articles-43/how-to-use-maql-count-in-gooddata-platform-301
Hesseltje van Goor
10/10/2022, 2:14 PMHowever, Jacques suggests a syntax of
SELECT COUNT(Employee ID, Records of Ticket Replies)Hesseltje van Goor
10/10/2022, 2:23 PMI can write
SELECT COUNT(ID (Portal Users), ID (VR))j
Jakub Sterba
10/10/2022, 2:30 PMHi, I suppose “ID (VR)” is unique key of a request. If this is the case you can do
SELECT COUNT( ID(VR) )SELECT COUNT(Records of Ticket Repliesh
Hesseltje van Goor
10/10/2022, 2:31 PMbut how do I get from counting the number of unique requests across the entire database to finding out the number of unique requests per applicant?
Hesseltje van Goor
10/10/2022, 2:31 PMi.e. how do I set a Count of ID per Portal User?
Hesseltje van Goor
10/10/2022, 2:32 PMI would like to know the average number of requests
j
Jakub Sterba
10/10/2022, 2:32 PMyou can use the same metric together with Portal User in “Rows”
h
Hesseltje van Goor
10/10/2022, 2:33 PMthe screenshot I shared came from the visual editor (analyse tab). How would I get it from a huge list to an average number, i.e.
last week, the average number of requests per portal users was x
j
Jakub Sterba
10/10/2022, 2:33 PMthe same metric can count all requests together, number of requests per portal user, number of requests per day etc.
h
Hesseltje van Goor
10/10/2022, 2:35 PMso basically if I wanted to do an average I would need to do:
COUNT (VR) / COUNT (USER ID)
j
Jakub Sterba
10/10/2022, 2:35 PMselect avg(select count(Records of Ticket Replies) by Portal User)h
Hesseltje van Goor
10/10/2022, 2:35 PMI don't have the Records of Viewing Request option in our system
j
Jakub Sterba
10/10/2022, 2:35 PMor
select count(Records of Ticket Replies)/count(Portal User)h
Hesseltje van Goor
10/10/2022, 2:35 PMi.e. we lack the equivalent to the metric that the article describes
j
Jakub Sterba
10/10/2022, 2:36 PMso which key identifies request in your data model?
h
Hesseltje van Goor
10/10/2022, 2:36 PMimage.png
j
Jakub Sterba
10/10/2022, 2:37 PMso you can define:
• metric Number of requests as`select count(id(VR))`
h
Hesseltje van Goor
10/10/2022, 2:38 PMyes
j
Jakub Sterba
10/10/2022, 2:38 PM• metric number of users as
select count(ID (Portal User)h
Hesseltje van Goor
10/10/2022, 2:39 PMand then COUNT (VR) / COUNT (USER ID)?
j
Jakub Sterba
10/10/2022, 2:42 PMand average requests per user as:
select Number of requests / Number of usersselect count(Id(VR))/count(Id (Portal User))select avg(select Number of Requests by Id(Portal user))select avg(select count(Id (VR)) by Id (Portal User)Jakub Sterba
10/10/2022, 2:43 PMI think the first two options will count users without requests as 0. The last two options will count average from users who had some requests.
h
Hesseltje van Goor
10/10/2022, 2:43 PMthank you, that's really helpful
j
Jakub Sterba
10/10/2022, 2:49 PMbtw. you can also count number of users with some requests (basically number of unique values in foreign key using:
select count(Id (Portal User), Id (VR))select count(Id(VR)) / count(Id (Portal User), Id (VR))h
Hesseltje van Goor
10/12/2022, 12:58 PM🤔 that is an interesting approach