To GoodData homepage

Metrics and MAQL

Metrics and MAQL

  • 100 Topics
  • 328 Replies

100 Topics

M
MarketaNew Participant
asked in Metrics and MAQL

Diff between Start Date and End Data

Hi, I am trying to do a diff between end and start date, I am trying this formula in metric: SELECT SUM(SELECT DATETIME_DIFF({label/startdate2.day}, {label/enddate2.day}) BY {dataset/flights}), and it doesnt show any error, but when I am trying to visualize, I am getting error trace: d9781182e7f70bd623d858893b240c73 { "title": "Bad Request", "status": 400, "detail": "A result cache error has occurred during the calculation of the result", "resultId": "db07993fb7512eae09bf975606466e10c948704c", "reason": "General error", "traceId": "d9781182e7f70bd623d858893b240c73"}

2
M
6 days 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
6 days ago
K
kaviKnown Participant
asked in Metrics and MAQL

Existing Metrics are not working

Hi, My current project is to create a unified workspace—a single workspace used by several clients. I had the opportunity to upgrade the current data model to include a new primary key in gooddata during that procedure. I noticed that some of the current metrics are not functioning and are displaying an error as I have given below after updating the data model.It would be more helpful if I get the quick and valuable response.  Thanks,Kavi

1
Michael Ullock
GoodData Employee
13 days 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
1 month ago
A
Andres SuttonNew Participant
asked in Metrics and MAQL

Calculate when an invoice is due

I have uploaded our invoice data to GoodData and would like to create a report where I will see when the invoices are due. I have invoice date and expiration days. Is it possible to calculate it and display as date "yyyy-mm-dd"?Example: Invoice date: 2023-01-01 Due: 15 days            Due date: 2023-01-16 Thanks in advance!!Andres

3
Moises Morales
GoodData Employee
1 month ago
bc1
bc1New Participant
asked in Metrics and MAQL

Displaying data within a timeframe

I have an attribute called Date Completed, which I can display no problem and an attribute with my current task status - I am able to see as of today is this task done or not, I am also able to see which month a task was marked completed. I am looking for a way to count tasks every month that are “open” aka in any status other than completed or do not have a date completed.

2
Julius Kos
GoodData Employee
4 months ago
anastasie.pustovalova
anastasie.pustovalovaNew Participant
asked in Metrics and MAQL

Grouping attribute values and then using the group to filter data

Hello,I need to create a groups which consist of atribute values. Then I want to use this group as a filter for my dashboard. How do I do that? Example:attribute=regionsnew attribute=states (groups of regions)filter = filters dashboard by states Thank you

4
anastasie.pustovalova
4 months 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
4 months ago
K
kaviKnown Participant
asked in Metrics and MAQL

Grouping Metrics into folder

Hi, In our system, we employ the LCM approach. Two new metrics that I created have been added to the folder in the DEV master workspace. I then executed the release and rollout bricks to implement the modifications in other client workspaces. Unfortunately, the supporting client workspaces do not have those 2 new metrics.Can somebody explain to me why this is acting in this way? Thanks,Kavi

4
Iva Gasparekova
GoodData Employee
6 months 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
7 months ago
M
Mr HolmesNew Participant
asked in Metrics and MAQL

How do I create a metric that identifies records that have a NULL value

Hi, I have a similar type question. I have a metric that counts the events whereby a conversation has been labeled with the attribute “NON-MVP” but I need a metric that counts the conversations that have not been labeled with the attribute “NON-MVP”. This is the code that identifies the label being used:select Inbound Handled Conversations where Conversation Label 8 [Conversation Attribute 8] like "%NON-MVP%"I tried using this code to identify conversations that have not been labeled “NON-MVP” which does not work:Select Inbound Handled Conversations where Conversation Label 8 [Conversation Attribute 8] not like "%NON-MVP%"Any help will be greatly appreciated.

3
Joseph Heun
GoodData Employee
7 months 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
8 months ago
R
RodrigoVM14New Participant
asked in Metrics and MAQL

Struggling with filters

Hi there,I need to build a table that brings the cumulative number of fired employees, by month, since the beggining of the company. I’ve managed to build the metric and the table brings the monthly numbers correctly, when I don’t apply a filter. However, when I filter this table to show only the last 12 months, the results are not cumulative anymore, it shows only the number of fired employees of that month alone. How can I overcome this?I’ve tried using many functions, like RUNSUM, BY, WITHIN, ROWS BETWEEN, but none of them seems to work.I’m new to MAQL and kind of struggling with this problem. Any help is welcome. Thanks!

4
R
8 months ago
B
B. LoganNew Participant
asked in Metrics and MAQL

Issue plotting RUNSUM without data at regular intervals

Hello,I’m attempting to plot fees + expenses to date by week to show project expenditure against budget. I’m using the following metric definition to create this specific trend. SELECT RUNSUM(IFNULL(Time Entry: Fees In Cents/Currency: Base Unit, 0)) + RUNSUM(IFNULL(Expense: Amount In Cents/Currency: Base Unit, 0)) with PF except Date (Shared)This works as expected when there is data every week, however not all projects will have expenses each week. When there is intermittent data, RUNSUM doesn’t graph correctly. Essentially there are holes in the graph, and as soon as there is data again the graph will continue, but leave a gap. This issue is not corrected by the “ifnull” statement in the metric definition. For example, the yellow trend below is fees + expenses to date, the green trend is fees to date. Fees to date is shown correctly because there are fees already entered for the week of Sun 3/19/2023, but there are no expenses yet so the yellow trend line stops at the previous week. I

3
Moises Morales
GoodData Employee
8 months ago
valerieholman
valerieholmanNew Participant
asked in Metrics and MAQL

Format Text

Is there formatting code to ‘wrap text’ in a column heading or an attribute? 

2
valerieholman
8 months ago
JeffC
JeffCParticipating Frequently
asked in Metrics and MAQL

Null Value - Unanswered Phone Calls missing

I have a table of phone call facts, that includes a foreign key to a users table.  When making dashboards, things have been going well as long as the phone call is answered - because the user_id field is populated and aligns with a worker.  Unfortunately, some calls are not answered so the user_id for that row’s column is null. Once I add the full_name to the insight, all of the rows missing a name due to the foreign key disappear (but are still included in the roll up).  I’ve referenced the how-to guide but something isn’t clicking for me how how to cater this to my call center use-case. Can anyone access my account (admin has access from prior ticket) to tell me what I may be doing wrong?  This is a stopper for our migration.

1
Moises Morales
GoodData Employee
8 months ago
JeffC
JeffCParticipating Frequently
asked in Metrics and MAQL

Date Time - Date Time

In our lake we store multiple date times, specifically for our use-case a postgress database with columns utilizing timestamp with time zone, nullable. call_start_time: 2023-01-14T16:32:25+00:00call_end_time:  2023-01-14T16:35:16+00:00 We don’t have a calculated column today, nor are we calculating at insert so we’re trying to create a metric that selects the average of call end time - call start time).  Since things may cross over the 24 hour mark and of we want to present data stored in UTC across different timezones, I’m looking for a way to make this work. In SQL it would be something like: SELECT AVG(TIMESTAMPDIFF(SECOND, call_start_time, call_end_time)) AS avg_time_not_answered FROM your_table_name WHERE Interaction_handled = 0; How can I replicate this in Gooddata, secondly, what is the easiest way to change visualization of our data into a selected timezone?

3
Radek Nováček
GoodData Employee
8 months ago
K
kmcdavidNew Participant
asked in Metrics and MAQL

Calculating Days Open/days past due

I’m trying to build metrics for days open and days past due. I tried following a similar thread however, its not working quite right. I used the following to calculate days open and I was able to create a report but it did not filter out any completed processes and I was unable to filter the results at all.Similarly I tried to follow the thread for days past due, creating the metric below, but when trying to create a report for it, it said I couldn’t because it would create nonsensical results. Am I missing something or is this not possible to do?

4
Moises Morales
GoodData Employee
8 months ago
M
Mark PetersonNew Participant
asked in Metrics and MAQL

Can you create an attribute based on the value of another attribute

Does anyone know if there’s a way of creating an Attribute based on the value of another attribute, or does this need to be created prior to hitting the GoodData platform? The "Calculated Attributes" won't fit this use-case. My use-case is, I have an Employee Table which contains an "Age" column. I'd like to assign an additional Attribute to each employee called "Age Bracket" and depending on the value in Age, would determine whether the "Age Bracket" value is Gen-X, Gen-Y, Gen-Z, or Baby Boomer. I know I can create metrics for each of those and count the unique employees, but the outcome I'm trying to achieve is a table view which shows for each Age Bracket: Count of employees, Average Salary, Average Tenure, etc. Like attached.Based on this previous article I’m assuming it can’t be done, but hoping something might have changed since this was listed over 1 year ago. Thanks

2
M
9 months ago
scott.jacobsen
scott.jacobsenParticipating Frequently
asked in Metrics and MAQL

How do you count records with effective dates

If I have a `users` table with effective dates, so the columns like this:id, start_date, end_dateAnd I want to count the users that were active anytime in a filtered date range in sql I would just doselect count(distinct(id)) from users where start_date <= {end of range} and end_date > {start of range}The values {end of range} and {start of range} would be based on whatever date filter the user selected in the dashboard - for example if they selected YTD {start of range} would be 2023-01-01 and end of range would be 2023-02-20I can’t figure out how to do this in the metrics editor though. Is it possible?

1
Moises Morales
GoodData Employee
9 months ago
M
Mark PetersonNew Participant
asked in Metrics and MAQL

Is it possible to create a Metric for the "last known value"

I’ve got a strange one that I’m wondering if anyone has seen and been able to find a way around.  I’m trying to build a dashboard which shows headline Economic stats from various datasets.  The problem is, the datasets are all updated at different frequencies, some monthly, some quarterly.  The figure I’m trying to display is the “most current value” for each of the metrics.  Some are current from this month (Feb 2023), others are current from last month (Jan 2023), and others are current from three months ago.  Without having to configure the KPI or Metric for a “static” date, can anyone think of a way of doing this?  Below screenshot shows an example of a couple of the datasets.  

2
Francisco Antunes
GoodData Employee
9 months 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
9 months 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
9 months 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
8 months ago
deedeedudu
deedeeduduNew Participant
asked in Metrics and MAQL

Sum by current year of subscription rather than calendar year

Hi team,I have a data model where we keep track of the customers accounts as well as the campaigns created by them.Customers have subscription_start_date and subscription_end_date as date dimensionsandCampaigns have created_at as date dimensionsFurther, Campaigns also have a Fact called as VolumeSubscriptions can run into multi years and the year (not the calendar year) of subscription starts from subscription_start_dateEg.A customer ABC, subscription_start_date = 04-Jul-2019, subscription_end_date= 04-Jul-2025Current year volume would be sum of volume of all the campaigns created after 04-Jul-2022 I want to create a metric that gives me the Volume for the current year of subscription There can be different customers with different subscription_start_date 

3
Joseph Heun
GoodData Employee
10 months ago
Welcome to the GoodData Community!Cookie settings