To GoodData homepage

Metrics and MAQL

Metrics and MAQL

  • 108 Topics
  • 347 Replies

108 Topics

Thiago Vivian Ribeiro
Thiago Vivian RibeiroNew Participant
asked in Metrics and MAQL

Help with RUNSUM

Hello! I’m heaving some problems to do a RUMSUN without an atribute Date. I’m trying to do it with an atribute of mt products:Basicaly i’m trying to an RUNSUM of that valeu for each product. The only way i could make summing all of then, but i need sum for after another.I know i can make this if the accumulate direct on the report but i need to make the metric.Does anyone knows how to code this? i tried many ways but none works. Thank you!

13
cristovan.camilo
11 months ago
P
plaubeKnown Participant
asked in Metrics and MAQL

How to use date metric into next filter metric - MAQL

Hello, please help me. Table Aid_table_a  INTa_date       DATE I would like to find rows for LAST MONTH a_date.  

12
Joseph Heun
GoodData Employee
2 years ago
L
LenaKnown Participant
asked in Metrics and MAQL

How can I custom a attribute or metric in Date format?

Hi,I want to get the latest Date group by a attribute in mm/dd/yyyy format.My code is :select MAX(Date (Ticket Created Date)) BY EntityIDAnd I edit “Metric format” to “mm/dd/yyyy” format, but it doesn’t work.Do you have any better idea?Thanks for your help!

9
Julius Kos
GoodData Employee
1 year ago
JeffC
JeffCParticipating Frequently
asked in Metrics and MAQL

Rollup/ Aggregation Wrong

I have a simple table that has an attribute of ‘Resource Direction’ and an attribute of ‘Call Sid’.  I’ve created a metric called Actual Calls Offered, and am trying to select and count all ‘Call Sids’ where Resource Direction = ‘inbound’ OR ‘Resource Direction = ‘inbound with attached outbound’.  The value comes back as 135 records, when I query the database I have 142.  When I add the insight, I get the wrong value of 135, but when I add a row to split the data, the row level data is accurate.  114 + 28 is NOT 135 as shown in the screenshot. SELECT COUNT(Call Sid) WHERE (Resource Direction = inbound OR Resource Direction = inbound with attached outbound)Edit - I tested in insights without using metrics and see the same roll up behavior that matches my metric, but seems accurate when SUM is utilized. 

8
Joseph Heun
GoodData Employee
1 year ago
V
Vasiliy OstrovskyKnown Participant
asked in Metrics and MAQL

Some metrics don't work

We're having trouble with a metric that calculates the ratio of the number of candidate_id with the status "Hired" to the total number of candidate_id. We first tried to evaluate this MAQL query, but it doesn't work.SELECT(SELECT COUNT({attribute/dshb_recruiting_ratio.job_posting_candidate_id}) WHERE {label/dshb_recruiting_ratio.application_status} = "Hired")/COUNT({attribute/dshb_recruiting_ratio.job_posting_candidate_id})Then we tried to calculate two independent  metricsand then to calculate the ratioSELECT TRUNC({metric/recruiting_ratio_status_hired} / {metric/recruiting_ration_total}, 4) or SELECT TRUNC({metric/recruiting_ratio_status_hired}, 4) / TRUNC({metric/recruiting_ration_total}, 4)This didn't work either. We received an error messagejava.sql.SQLException: exception while executing query: UNSUPPORTED_OPERATION ERROR: This query cannot be planned possibly due to either a cartesian join or an inequality join. If a cartesian or inequality join is used intentionally, set the op

7
U
GoodData Employee
2 years ago
L
LenaKnown Participant
asked in Metrics and MAQL

Custom a DateDifff Key performance Indicator in MAQL

Hi there everyone, I want to be able to average number of days between Listing Date and Today. My metric looks as follow: Then I put the metric AVG of Vacant Days on dashboard and it has an error:Is it possible to calculate directly on Gooddata instead of creating a view in DW?Thanks a lot,Lena

7
L
2 years ago
M
MashaKnown Participant
asked in Metrics and MAQL

Working with metrics

Hello,I have a question regarding the working with metrics topic. I have read through documentation related to this topic but it's still unclear to me why Attributes become unavailable when I pick a metric that I created. I'm trying the basic concept of creating a metric by summing up 2 facts from different fact tables of the data model. Both tables are related to one dimension table (that stores all attributes) as 1:N.The interesting part for me is that if I'm using these same 2 facts separately, most of the attributes remain available.  Please see the screenshots below. 

7
Julius Kos
GoodData Employee
1 year ago
Douglas Paiva
Douglas PaivaNew Participant
asked in Metrics and MAQL

Percentage of each item based on total

Guys, how are you? I'm trying to create a column that shows the percentage of each item's quantity based on the total sum of quantity. I'm not getting success... if I put the percentage directly in the insight, I can't add another column with integer values. Is there any metric that brings me these values?Attempt to create a metric...SELECT (SELECT SUM(Quantidade) BY Tenant, ALL OTHER WITHOUT PF) / (SELECT SUM(Quantidade))Trying through Analyze- This is the desired column  - But when adding the quantity column in integer format, it disables the "show in %" option 

7
Joseph Heun
GoodData Employee
11 months ago
A
Augusto SilvaNew Participant
asked in Metrics and MAQL

Building of an MAQL for Rolling Average

We have a table with several records where we need to perform the moving average of the last 90 days considering only grouping by one column (fluxo_id_card).If we don't consider the rolling avarage, the calculation is performed as follows:But what happens is that we need to display these results in a month-to-month progression, where each month we view the average of the last 90 days.For the Rolling average, the basic syntax is:But we don’t have a certain number of lines that each card would occupy, and that Rows Between does not currently support the COUNT function.How can we adapt this calculation to the new context without changing the original table in the model?

7
A
2 years ago
C
CarsonParticipating Frequently
asked in Metrics and MAQL

Query Date Field for Headline insight

HI All,I would like to show a date as a headline component. Is this possible?I am using GDCN 1.6 E.g. from my dataset, I want to show what is the `select max(date)`

6
Joseph Heun
GoodData Employee
2 years ago
jamesjenkins
jamesjenkinsKnown Participant
asked in Metrics and MAQL

Average age from date of birth

I am trying to find the average age of customers from date of birth but struggling. Any pointers would be appriecated. I have looked at the following two webpages but still just keep getting errors. I imagine it will look something like this. SELECT AVG(SELECT Date({metric/this_year}) - {label/DateOfBirth.year} ) https://help.gooddata.com/doc/enterprise/en/dashboards-and-insights/maql-analytical-query-language/maql-use-cases-and-tutorials/date-attribute-arithmetic/

6
jamesjenkins
9 months ago
Jay Finegan
Jay FineganParticipating Frequently
asked in Metrics and MAQL

Counting records by metric

A common hypothesis is that employees with less experience are involved in more accidents. As the following image shows, I can identify specific incidents within a given time frame, I can compute the employee’s length of service (time delta between the date-of-hire and the event date), and I can properly assign the incident to a length-of-service bucket. WHat I am struggliung with is how to count the incidents in each bucket. For this example, I would like to be able to show a bar or pie chart which shows bucket ==> count:0 ==> 22 ==> 55 ==> 210 ==> 125 ==> 3Any ideas?

6
Francisco Antunes
GoodData Employee
6 months ago
L
Linda JInspiring
asked in Metrics and MAQL

Prior year calculation

Hello,I have been able to create a metric to compute prior year - but am having an issue with filtering - see screen shotI need to be able to see the prior year values for Dec, Jan and Feb  - If I ignore the parent filter I get values for all time

5
Iva Gasparekova
GoodData Employee
3 years ago
K
kaviKnown Participant
asked in Metrics and MAQL

Accept the date while year starts with 0

how to recognise any four-digit numeric value as the year while the data is loading into client workspaces, for example, 0202

5
K
1 year ago
K
kaviKnown Participant
asked in Metrics and MAQL

Migrate Computed Attributes between Workspaces

Hi, I'm trying to migrate computed attributes from STG to PROD workspaces, but after step 6 (attached link below), I'm unable to proceed because it is not providing any scripts.(Please find the attached screenshot)https://help.gooddata.com/doc/enterprise/en/workspace-and-user-administration/managing-workspace-data-and-data-access/manage-attributes/use-computed-attributes/Environment: Gooddata PlatformPlease help me on how to resolve this. Thanks,Kavi 

5
Francisco Antunes
GoodData Employee
1 year ago
Jan
JanNew Participant
asked in Metrics and MAQL

metric not available

Hello,I have created a simple metric (just a simple logic between attribute and fact). The Metric is available in the `Analyze` section along other facts and attributes.However if I try to construct f.e. `Line chart` and drag this metric to `Metrics` section, all other attributes disappear and I’m unable to use them (f.e. I’m unable to `Trend by` Date, etc...). Also, if I first try to `Trend by` Date, the added metric disappears from the available data and I see the warning `1 unrelated data item hidden`. Obviously, somehow the metrics are not connected with other data - how can I please achieve that? Haven’t found any relevant posts about that in the docs nor the community…Thanks.

5
Jan Kos
GoodData Employee
2 years ago
jeferson.nunes
jeferson.nunesParticipating Frequently
asked in Metrics and MAQL

METRICS MAQL - AVERAGE SALES OF THE LAST 10 DAYS

Good Morning!I need help to make a metic where I need to display the average sales for the last 10 days I tried to do it this way and I was not successful SELECT AVG(Sales WHERE (Date “in this case the day”) BETWEEN THIS - 10 AND THIS)In short, I need to return the average sales volume of the last 10 days.Thank you very much in advance!

5
Iva Gasparekova
GoodData Employee
2 years ago
J
JRAlvesNew Participant
asked in Metrics and MAQL

Number of days of a customer's last purchase

Hi everone,I'm trying to develop a metric that shows the number of days of a customer's last purchase.I tried to do it, for this way:SELECT max (Date (Data de saída da nota fiscal) - this)but this way, I need put in report, the attribute Date (Data de saída da nota fiscal) and not only a metric by customer name. If I don´t put the attribute, the report return error. Thanks for help. Alves.

5
J
2 years ago
P
Preetham ThangammaKnown Participant
asked in Metrics and MAQL

How to display from 1 to 10 in the order which is an attribute column?

Hi,   Please let me know how to display from 1 to 10 in the correct order because in the attachment 10 is displaying after 1 which is incorrect. And this is an attribute column. Thank You

5
Iva Gasparekova
GoodData Employee
5 months ago
upkeeply
upkeeplyNew Participant
asked in Metrics and MAQL

How difficult is GoodData to learn for non-technical users?

Would like to use in in a multi-tenant environment, but sometimes the users are not too tech savvy.

5
Iva Gasparekova
GoodData Employee
1 year ago
L
LenaKnown Participant
asked in Metrics and MAQL

Lookup a value not exists in the attribute value list of an attribute

Hi there,I’m creating a metric with a condition that is looking for a value in the attribute value list, but that value doesn't exist in the Attribute values appearance, my case: ‘Vacant’ value doesn’t appear at this time, maybe it will be in the future.How could I use it anyway?Thank you!

5
Iva Gasparekova
GoodData Employee
2 years ago
Jay Finegan
Jay FineganParticipating Frequently
asked in Metrics and MAQL

RUNSUM doesn't seem to like intermittent data

GoodData Enterprise;Metrics in play include:# Incidents_Recordable: SELECT IFNULL(COUNT(incidentID),0) WHERE isRecordable=1 AND Date (Event) BETWEEN _Timeline_Start AND _Timeline_EndYTD Recordables: SELECT IFNULL(RUNSUM(# Incidents_Recordable), 0) BY Month (Event) WHERE Date (Event) BETWEEN _Timeline_Start AND _Timeline_End Here is the data table where the issue manifests itself:The highligted elements in the data table show the YTD count dropping to zero, rather than preserving the YTD from the previous period. Cleveland, for example, should remain a 2 through March and April, as the YTD count was 2 at the end fo February. I’ll note that if Cleveland had an incident in May (outside the range of the image), the YTD count would (and does) correctly reflect the value of 3. Analysis shows that these “gaps” occur whenever a month has no incidents (and there is at least 1 incident in a predecessor month). It is clear that the RUNSUM function works correctly when there is an essentially cont

5
B
1 year ago
J
Jean-Christophe
asked in Metrics and MAQL

Specific SQL keyword

Hi, Is there a a way to do a MINUS/EXCEPT like in SQL ?Objective is to count all users that are having event for something but not for something else.example :    SELECT DISTINCT LEARNER from ...    WHERE USER_ACTION_FACT_TYPE  in ('Bookmark', 'Share', 'Comment', 'Like','ArticleView'  )    MINUS    SELECT DISTINCT LEARNER from ...    WHERE USER_ACTION_FACT_TYPE in ('Training Session' ) What about INTERSECT ?    SELECT DISTINCT LEARNER from ...    WHERE USER_ACTION_FACT_TYPE  in ('Bookmark', 'Share', 'Comment', 'Like','ArticleView'  )    INTERSECT    SELECT DISTINCT LEARNER from ...    WHERE USER_ACTION_FACT_TYPE in ('Training Session' ) Best regardsJean-Christophe 

5
Joseph Heun
GoodData Employee
1 year ago
RenaudD
RenaudDNew Participant
asked in Metrics and MAQL

Metric to count periods

Hello,I seek for help because I don’t know if it is just impossible by MAQL or if I spent hours doing something wrong. Can you please help me ?We have a dataset like the one below. The users are registered to a specific training course. Each registration have a starting date. The end can be null or set to a specific date.Training Course User Registration Start (Shared Date) Registration End Onboarding A 2022-12-01 2023-12-31 Onboarding B 2023-07-01 null Onboarding C 2024-01-01 null Onboarding D 2023-12-01 2024-02-2024 Onboarding E 2022-12-01 null  I want to have a metric to calculate the number of open registrations. It must count a registration if his end date is null or above the minimum date in the filter, and the start date must be lower than the maximum date in the filter. It can give something like this : (Date(Registration End) >= Start_Date OR Date(Registration End) = (empty value)) AND Date(Registration Start) <= End_Date

5
Francisco Antunes
GoodData Employee
9 days ago
P
plaubeKnown Participant
asked in Metrics and MAQL

How use MAQL command Count with similar condition SQL (NOT EXISTS)

Hello, please help me, how to correctly write this SELECT in MAQL. SQL:select count(*) FROM table_a WHERE NOT EXISTS(select * FROM table_b WHERE table_b.id_table_a = table_a.id_table_a)  Thank you very muchPetr

5
Joseph Heun
GoodData Employee
2 years ago
Welcome to the GoodData Community!Cookie settings