To GoodData homepage

Metrics and MAQL

Metrics and MAQL

  • 100 Topics
  • 328 Replies

100 Topics

A
Allan TalverNew Participant
asked in Metrics and MAQL

Linking two unrelated datasets in MAQL

Hello,I am attempting and failing to write some MAQL queries.I have two datasets with the following fields (model simplified from the real world case, which has many more attributes for contracts):Contracts Vendor name Contract currency Contract value in contract currency Exchange Rates Currency Date Rate The Exchange Rates are stored as a rate between the respective currency and our company main currency. The exchange rate for the main currency being stored as 1.I need to build a report which shows list of contracts (by vendors) with several metrics, including contract value:in our company base currency in the currency selected in a dropdown selector of the dashboard (the exchange rate for the current date should be used for conversion)I have been trying to build the metrics, but I have not succeeded yet. I have started with creating a metric which would get the current day exchange rate for each contract:SELECT SUM( SELECT SUM(Rate) BY Date, Currency ALL OTHER )BY ALL OTHEREXC

3
U
GoodData Employee
2 years ago
R
Rafael CruzNew Participant
asked in Metrics and MAQL

Age of employees

Hello, how do I get the age of the collaborators? I already have the date of birth.

4
Martin Burian
Community Manager
2 years ago
jeferson.nunes
jeferson.nunesParticipating Frequently
asked in Metrics and MAQL

Data update

Good Morning! Would it be possible to create a metric that would display the date and time of the last time the data was updated? Last time the dataset was updated within GoodData? If it were possible to create a metric I would display it inside a dashboard, showing "This data has been updated XX/XX/XXX at 00:00

2
jeferson.nunes
2 years ago
G
giripurnamaNew Participant
asked in Metrics and MAQL

MAQL check past date

Hello, I have a question about MAQL.Is the MAQL syntax able to check the past date?Example:I have a date of 27/06/2021 then I want to check whether the date has passed 3 days or not.

1
J
GoodData Employee
2 years ago
jeferson.nunes
jeferson.nunesParticipating Frequently
asked in Metrics and MAQL

Buy sales this month with the same month last year

Good Morning! I need to create a metric to compare sales for this month (until today) with sales for the same month last year, but in equal periods.I made a metric that compares the months, but I don't buy in equal periods being:SELECT IFNULL(SUM(SALES),0) FOR PREVIOUS (Month/Year (Data),12)❌ So he compares the following dates:06/01/2021 until 06/23/2021 (today) x 06/01/2020 until 06/30/2020.✔️ I need him to compare the same dates being:06/01/2021 until 06/23/2021 (today) x 06/01/2020 until 06/23/2020 (today). Thank you so much for your help!

2
jeferson.nunes
2 years ago
K
GoodData Employee
Kyle ShimekGoodData Employee
published in Metrics and MAQL

Creating Measures that Override Filters in GoodData.CN

When creating measures and designing dashboards in GoodData.CN, there may be times where you want a calculated value to remain the same after applying a dashboard filter. This is possible by creating measures with the WITHOUT PARENT FILTER keyword in MAQL.  This keyword prevents any so-called Parent Filters from applying towards the measure. A Parent Filter is a filter that is applied to an object that uses the measure, such as an insight or dashboard.  The keyword can take the following forms: SELECT … WITHOUT PARENT FILTERSELECT … WITHOUT PARENT FILTER EXCEPT …SELECT … WITH PARENT FILTERSELECT … WITH PARENT FILTER EXCEPT … In the MAQL syntax, “PARENT FILTER” can also be abbreviated as “PF”. So, “WITHOUT PARENT FILTER” can also be written as “WITHOUT PF”.  In this article, I will lay out a few common examples of when you might use this feature. You can read more in our documentation. Common use casesOne use case is to have a dashboard with filterable insights where one or more insight

1080
K
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
A
Andrew IlinNew Participant
asked in Metrics and MAQL

GoodData.CN Community Edition / MAQL metric calculation

HiThis article provides an example of writing a request to the system - https://www.gooddata.com/developers/cloud-native-community-edition/ like Active Users:SELECT COUNT(User) WHERE User Status = "Active"We have installed the system and cannot find a way to make such a request. Are they available there? Where could we go wrong?Thanks!

3
Iva Gasparekova
GoodData Employee
2 years ago
L
Linda JInspiring
asked in Metrics and MAQL

Date Math

Hello -I am trying to create a metric for a date last yearfor example my selected date filter is 2/01/2021 - I want the answer to be 2/01/2020 I also would like to be able to subtract months as well so 02/01/2021 would be 01/01/2021 Thank you

3
Iva Gasparekova
GoodData Employee
2 years ago
L
Linda JInspiring
asked in Metrics and MAQL

Use filter as a parameter

Hello, I would like to be able to change the metric displayed on the report based on a paramenter.For exampleA drop down box on the Dashboard has 2 possible selections A and B - only 1 can be selectedIf A is selected the formula for the metric is Z+YIf B is selected the formula for the metric is Z-Y I believe I would have to start by loading a phony table with just the values of A and B - and then add a filter onto the dashboard for the selection.  But after that I am stuck. Thanks for your help

2
L
2 years ago
J
Julia KNew Participant
asked in Metrics and MAQL

Timestamps - I need to calculate how many users were active each minute, having single intervals of their activity

Hello!The problem I’m struggling is how to create an AMR (Average Minute Rating) using MAQL. For simplicity I started with one day period when I want to observe how users activity changes over time (for example minute by minute). So I have data of a form:userID start time - atribute refering to Start time table having M, H, S values as a facts, start time label as a key HH:MM:SS and an atribute start time [min] HH:MM start day - date  end time - atribute refering to table with a structure like Start time table already described end day - date  activity detailsI was able to build a very inelegant vay to find out how many unique users were active during particular hour (e.g 20:45  HOUR = 20, MINUTE = 45), but with one major drowback which is that I need hour and minute as a variable to do those calculations. Here’s the MAQL form:I need solution that could be visualised - for every minute, automatically. Hoping for your help! I would appreciate any hints :)

3
Boris
GoodData Employee
2 years 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
2 years ago
L
Linda JInspiring
asked in Metrics and MAQL

Help with Previous year calculation

Hello, I am trying to create a calculation to show the variance between the select time period (based on the dashboard filter) and the previous year.  It looks like I need to use the previous function - but I am receiving the below error.  When I use this calculation the result is always zeroSelect SalesCredit Business Line- SalesCredit Business Line where Year (Billingdatekey) = Previous   

4
L
2 years ago
K
Konstantin KomissarchikNew Participant
asked in Metrics and MAQL

Zero values in bar chart insights

I have a bar chart that is using the basic “count of id attribute” for the measure. The problem that I have is that some of the “view by” values are missing from the chart if their count happens to be zero. I need to show all possible values of the “view by” column on the chart, even if their count is zero.

4
K
2 years ago
T
TommyGNew Participant
asked in Metrics and MAQL

DateDiff in MAQL?

Hi there everyone,I was just wondering if there is a simple way to perform a DateDiff function in MAQL. More specifically I want to be able to count number of days between Order Date and Today. I will be using Order Status to determine whether the order was completed and this MAQL metric would help me in highlighting those that take too long. Thanks a lot,Tomas

1
Joseph Heun
GoodData Employee
2 years ago
A
AshleighNew Participant
asked in Metrics and MAQL

Difficulty creating new MAQL metric based on Customer Segment

Hi,I have a dataset containing sale orders, which includes:Order Date - Date the order was placed Customer ID - Unique ID for each customer Order ID - Unique ID for each order Customer Type - ‘New’ if it’s the first order placed by the customer, ‘Repeat’ for all subsequent orders £ Amount - Total order amount in GBPWe would like to introduce a Customer Segment, which is dependent on the reporting period (week/month/year) and is defined as:‘New’ if the customer placed their first order within the reporting period (regardless of whether they also placed repeat orders within the reporting period) ‘Existing’ if the customer placed their first order before the reporting periodI want to create a metric ‘£ New Customer Sales Monthly’ which is the SUM of the sale order amounts (£ Amount) for New customers each month.Note - if the customer places their first order in a given month and places repeat orders within that same month, then all of those sale order amounts should be included in the ‘£

3
A
2 years ago
A
Allan TalverNew Participant
asked in Metrics and MAQL

Struggling with MAQL (aggregation and filtering)

Hello,I have:A fact named Workday (values 1 and 0). The dimensions attached to the fact: Company, Employee, Date. Companies are essentially our subsidiaries in different countries where different holidays are applicable, resulting in differences in maximum available Workdays.In my report I want to see metrics:A: the number of Workdays per Employee in a selected time period B: number of MAX Workdays any Employee had in the sae Company and in the same time period (basically how many workdays were available if a specific employee had worked on all possible days) C: % of A/BI am struggling to create a metric for B where I want to find the maximum number of Workdays any Employee in the same Company had within a chosen time period.The closest to what I need is when I define the metric B as follows:SELECT MAX(SELECT SUM(Workdays) BY Employee WITHOUT PF EXCEPT Date (Worked Date)) BY Company ALL OTHERHowever, as soon as I filter the report for any subgroup (specific employee, team, etc.) the

1
Petr Olmer
GoodData Employee
2 years ago
M
Manu MeicNew Participant
asked in Metrics and MAQL

Results locked by attribute

Hello! I’m having a problem trying to lock the agregation level in a result. I have a report showing : store, articule, sales and stock. I need that the sales metric doesn’t change despite the store.For example: if one articule had 3 sales in one store and 2 sales in other one, the report should show 5 sales no mather what store I am looking. 

1
Petr Olmer
GoodData Employee
2 years ago
S
suseteNew Participant
asked in Metrics and MAQL

How to get data from a specific table not in a child table? For example, all Users without any Skill?

How to get data from a specific table not in a child table? For example, all Users without any Skill?

1
Michael Ullock
GoodData Employee
2 years ago
L
LenaKnown Participant
asked in Metrics and MAQL

Cannot pull multiple Date dimensions into one Insight

Here is my Logical Data Model:For each WorkOrder have Ticket Created Date, Wo Created Date,… dimensions, when I add Ticket Created Date dimension then I can not use  Wo Created Date or others in the same insight. I have to work around that I created a view to convert other Dates to text to display on the report (Ticket Created Date Text, Wo Created Date text,...).Do we have any solution or better ways of this? Please help me.Thank you so much!

4
L
2 years ago
S
Simone HaasNew Participant
asked in Metrics and MAQL

Calculating how many days are invoices past due

Hello there. I uploaded our invoice data into GoodData and I would like to create a report where I will see how many days are open invoices past due. I do not have this number in my source data, I have just due date. Is it possible to calculate it? Thank you. Simone

2
S
2 years ago
J
JamesParticipating Frequently
asked in Metrics and MAQL

How To Ensure A Metric Available In Analytics Designer

Hi I created a metric based on two values that I’m using in an insight. However, when I use the metric ( a measure that simply chooses which of the other two measures should be shown) I get a message saying “Sorry, We Can’t Display This Insight”. And then when I use other data values, it moves into “unrelated data measures”  Do the metrics need to be applied to the LDM? Or do I need to somehow associate the metric with a given dataset? Thanks James 

2
Pavel
3 years ago
L
GoodData Employee
Leilani GreerGoodData Employee
asked in Metrics and MAQL

Metric vs Measure and Best Practices

What is the difference between Metric and Measures .... Are there best practices around when to use which one ?

2
Pavel
3 years ago
K
Keith FinnNew Participant
asked in Metrics and MAQL

Metric visibility

My colleague created a metric, but I can't find it. Any idea why?

1
Iva Gasparekova
GoodData Employee
3 years ago
M
Mason PriceNew Participant
asked in Metrics and MAQL

How to create reusable MAQL metrics/measures for Analytical Designer?

I would like to create some reusable MAQL metrics/measures using my data, but there are just very basic aggregations available in the Analytical designer. How can I use MAQL in there and make the metric/measure reusable?

1
Martin Burian
Community Manager
3 years ago
Welcome to the GoodData Community!Cookie settings