To GoodData homepage

Metrics and MAQL

Metrics and MAQL

  • 106 Topics
  • 340 Replies

106 Topics

G
gaurav.patilNew Participant
asked in Metrics and MAQL

Update Variable Value dynamically

Hello I am using GoodData for our application. My question is related to variable and update its value dynamically.I will explain you what I have done so far.I have created variable with name organisation_id and set type to filter variable and selected attribute as a id column of one of our database table (Please check attached image - 1)After that I have set user-specific value for that variable e.g. organisation_id = 12, 34, 56 etc.After that I have create custom matrix and use this variable in MAQLe.g. SELECT COUNT(id) WHERE organisation_id(Please check attached image - 2)And then I have added this matrix in insights and in the widget and it filter the data which related to value of organisation_id variable i.e. it only display data related to id - 12, 34, 56 My Question is:Can we update variable value using API?like in above example we set organisation_id = 12, 34, 56now I want to update organisation_id = 89, 90 and get result related to 89 and 90Instead of updating organisation_id

1
Joseph Heun
GoodData Employee
2 years ago
K
GoodData Employee
Kyle ShimekGoodData Employee
published in Metrics and MAQL

Creating measures with conditional expressions using the IF keyword

With MAQL, you have the ability to create measures that use conditional expressions, allowing you to compute your more complex business requirements. One way to do so is to use the IF keyword. It looks like this:SELECT IF condition THEN expression ELSE expression END The IF keyword works just like it sounds: you will set a condition after “IF”, the value to use if that condition is met after “THEN”, and the value to use if the condition is not met after “ELSE”. Finally, you end the expression with “END”. The IF keyword can be used in both GoodData Platform and GoodData.CN.  Here are a few examples of measures we can create in the metric editor: SELECT IF AVG(Duration) > 60 THEN 1 ELSE 0 ENDSELECT IF Category = “Email” THEN SUM(Budget - Spend) ELSE SUM(Spend) ENDSELECT SUM(SELECT IF Cost > 200 AND Supplier = “Acme Products” THEN Price * 2 ELSE Price END) And those same examples as created in GoodData.CN using the API:SELECT IF AVG({fact/call_center.duration}) > 60 THEN 1 ELSE 0

980
K
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
R
Ron BorensteinNew Participant
asked in Metrics and MAQL

Top 10 results per day

I have a measure called `timeSpent` which is time a user spent in seconds on a page.I have attribute called domain which is the site the user visited, and I have the date.I want to create a stacked bar chart showing the sum of timeSpent for each domain on each day. As there are too many domains I try to filter the top 10. However, that returns the top 10 overall, and I would like to show the top 10 sites per each of the days.I went over the maql courses, and tried many different things, and I am struggling. I think that row_number could somehow help here, or at least that’s how I would have solved it in sql, but I am not sure how to use it here.  

2
R
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
S
Sergio WeinhardtNew Participant
asked in Metrics and MAQL

Metric that sums up another metric for a period defined by a variable

I would like to know how to create a metric that sums up another metric for a period defined by a variable.Example:DataMetric A = SalesMetric B = date (inventary) ObjetivoMétrica C = Sales by period between inventaries                 Metric A            Metric B                            Metric C                Sales                Inventary            Salesda by period (Inventary)01/01    100                         0                                             002/01    50                           1                                             15003/01    125                         0                                             004/01    75                           0                                             005/01    150                         1                                             350        06/01    100                         0                                             007/01    25                           0                                             0 Thanks

1
Moises Morales
GoodData Employee
2 years ago
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

1160
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
3 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
3 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
3 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
3 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
3 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
3 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
3 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
3 years ago
Welcome to the GoodData Community!Cookie settings