To GoodData homepage

Metrics and MAQL

Metrics and MAQL

  • 87 Topics
  • 272 Replies

87 Topics

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

2
B
13 hours 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
14 hours ago
Jay Finegan
Jay FineganNew Participant
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
10 days 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
19 days 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
21 days 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
21 days 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
Community Manager
21 days 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
1 month 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 month 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
1 month 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
1 month 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 month 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
2 months 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
2 months ago
J
Jean-Christophe
asked in Metrics and MAQL

List Unused object

Hi All, Is there a way from the grey pages to list all unused metrics in KPIs, Insights, other metrics… ?Without having to check metric one by one using the Object/Used button.  Best regardsJean-Christophe 

3
Iva Gasparekova
GoodData Employee
2 months ago
J
Jean-Christophe
asked in Metrics and MAQL

Last session date

Hi all, Trying to show the last session date for each learner. I have followed this proposal to create a metric based on a date from https://help.gooddata.com/classic/en/dashboards-and-insights/maql-analytical-query-language/maql-use-cases-and-tutorials/finding-min-and-max-dates-using-date-arithmeticI use : SELECT MAX ((SELECT (100000 * MAX(Day of Month (Timestamp))) + (100 * MAX(Month (Timestamp))) + (MAX(Year (Timestamp))-2022+2022) BY Coaching Session Uuid)) WHERE (SELECT COUNT(Date (Timestamp), Coaching Session Uuid) BY Year (Timestamp), ALL OTHER) > 0and 0#/0#/#### (day/month.year format) for the format but I get crazy dates. Not sure what are these 1000000 and 100 in the metric. 

4
J
4 months ago
Douglas Paiva
Douglas PaivaNew Participant
asked in Metrics and MAQL

Use more than 4 decimal places

Hello guys!Could you help me? I have to calculate the value of the cost x amount of information in our database. I'm having difficulties because the cost has more than 4 decimal places, as shown in the image below. I've tried using the Property templates, but without success.Note: It is set to float

1
Julius Kos
GoodData Employee
4 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
4 months ago
Douglas Paiva
Douglas PaivaNew Participant
asked in Metrics and MAQL

Add column with comparative percentage of years

Hi guys!I would like to know how I can add a column that presents the percentage calculation comparing the values ​​of a year with the previous year, as shown in the table below. I only need three columns... if possible to show in "green" the growth and in "red" the fall. Could you help me please?

3
Joseph Heun
GoodData Employee
4 months ago
C
courtneyNew Participant
asked in Metrics and MAQL

Users in project

Hello, I need to find information related to each of the users in my project. I am going to https://secure.gooddata.com/gdc/projects/{workspace_id}/users but it is asking me for my username and password - which is not accepted when I enter it. I get the following error:{ "component" : "auth-service", "message" : "Bad Login or Password!", "parameters" : [ ]} I am definitely entering the correct username (my email) and password associated with my GoodData account. Please can you tell me how to access user information - I need this to be able to set up data permissions. Best,Courtney

10
Joseph Heun
GoodData Employee
4 months ago
GuilhermeNascimento26
GuilhermeNascimento26New Participant
asked in Metrics and MAQL

Show first date, filtering by branch

Good afternoonI'm trying to show the first issue date of a given document, filtering by branch.Does anyone know if it's possible?

1
Moises Morales
GoodData Employee
4 months ago
Thiago Ferreira
Thiago FerreiraNew Participant
asked in Metrics and MAQL

Diference between dates

Good morning, Is there any way to count the difference in days of two dates without counting holidays?

1
Moises Morales
GoodData Employee
4 months ago
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!

3
Moises Morales
GoodData Employee
5 months ago
GabrielGreco
GabrielGrecoNew Participant
asked in Metrics and MAQL

See MAQL codes made by "Analyze"

Hey!So, I would like to know if there is any way to see the MAQL code made up by the drag & drop analyze. I created a insight using the drag & drop tool but I need to see the Metric code (SELECT) that was generated by the tool to use at other projects. Thanks!

3
Julius Kos
GoodData Employee
5 months ago
J
JeffCatNew Participant
asked in Metrics and MAQL

Color format HH:MM:SS

Is there a way to color format HH:MM:SS?I’m looking for a way to evaluate this and color format accordinglyI’d like to do something like this:[<3600][red]{[>=3600]{{{3600||00}}}:{{{60|60|00}}}:{{{|60.|00}}};[<3600]{{{3600||00}}}:{{{60|60|00}}}:{{{|60.|00}}};[>=0]{{{60|60|00}}}:{{{|60.|00}}};[<0]-{{{60|60|00}}}:{{{|60.|00}}};};[<18000][Yellow]{[>=3600]{{{3600||00}}}:{{{60|60|00}}}:{{{|60.|00}}};[<3600]{{{3600||00}}}:{{{60|60|00}}}:{{{|60.|00}}};[>=0]{{{60|60|00}}}:{{{|60.|00}}};[<0]-{{{60|60|00}}}:{{{|60.|00}}};};[<36000][green]{[>=3600]{{{3600||00}}}:{{{60|60|00}}}:{{{|60.|00}}};[<3600]{{{3600||00}}}:{{{60|60|00}}}:{{{|60.|00}}};[>=0]{{{60|60|00}}}:{{{|60.|00}}};[<0]-{{{60|60|00}}}:{{{|60.|00}}};};

2
J
6 months ago
Welcome to the GoodData Community!Cookie settings