To GoodData homepage

Metrics and MAQL

Metrics and MAQL

  • 107 Topics
  • 341 Replies

107 Topics

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
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
1 year ago
M
Ma'ayanNew Participant
asked in Metrics and MAQL

Rollup Methodology

Hi community,Looking to get some clarity around what the “Rollup” aggregate methodology is, and why it would differ from an Average aggregate function. See the attached Insight, where Average and Rollup are different, but very similar. I couldn’t find any specific information in the Documentation section. Thank you in advance!-Ma’ayan, Product Manager, GMV  

4
Joseph Heun
GoodData Employee
2 years 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
11 months ago
A
anastasie.pustovalova
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
A
8 months 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
Emmanuel
EmmanuelNew Participant
asked in Metrics and MAQL

Median query not working

Hi, I have a set of conversations logged in with DATE attribute. I’d like to measure the median count of conversation per day. However, when I enter something like  SELECT MEDIAN(COUNT(conversation_id)) BY Date (Start Date)it doesn’t seem to work, and instead give me the total count, not the median count by date. Am I missing something? How could I get the median count of conversations? Thanks so much

4
Emmanuel
1 year ago
Jay Finegan
Jay FineganParticipating Frequently
asked in Metrics and MAQL

Single dashboard, two KPIs, use a different time frames

We have several clients who would like to see two KPI/s on a single dashboard: the first shows a statistic for the last 12 months, the second shows the same statistic for the last month. I know that we can build a single KPI and show it on two dashboards (each with its own timeline), and I know how to build KPIs that include/exclude parent filters. But I have been unable to figure out how to have a KPI rely on a parent filter in one case while another KPI on the same dashboard ignores the parent filter while simultaneously substituting an independent timeframe. We have a system attribute called Timeline and our development team has defined two system metrics, _Timeline_Start and _Timeline_End. These metrics were created from Timeline (Min(Date(Timeline) and Max(Date(Timeline), respectively). To me, the latter would be seem to be of particular importance to this inquiry, presuming that the time line for the common dashboard is selected as the 12-month window. Here is an example of the t

4
Joseph Heun
GoodData Employee
1 year ago
M
MarketaParticipating Frequently
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"}

4
M
4 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
1 year ago
P
PD5Known Participant
asked in Metrics and MAQL

How to filter by 2 different dates

I have a data model with orders and refunds in separate tables. I would like to use MAQL to create a metric showing the sum of all the order totals, less the sum of all the refund totals for a particular date and/or date range. The problem is that the two tables are using different dates. The orders table has “date the order was created” and the refunds table has “date the refund was created”. This is fairly simple in pure SQL using 2 sub-queried, Is there a way of making this work in an MAQL query? Edit: I should also mention that the order refunds table is linked to the orders table via the order id which complicates things further.   

4
Joseph Heun
GoodData Employee
2 years ago
A
Alejandro BNew Participant
asked in Metrics and MAQL

Last day of the selected month

Hello, I would need to create a metric to bring me the last day of the month that I am selecting in the filter of a report.For example, If I select the day March-18-2021, it should bring me the number 31.If I select the day April-14-2021, it should bring me the number 30.And so on, 28 in February (or 29 if that year is a leap year).Thank you very much!

4
A
2 years 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
1 year ago
F
FilipNew Participant
asked in Metrics and MAQL

Use Filter/Parameters to switch between metrics

Hi there,Let's say I have a table with four columns - Year, Organisation, Sales, Cost.I want to create a slicer with two values - Sales and Cost, and an option to select only one of those.If Sales value is selected from the slicer then I want my metric to display sum(Sales), if Cost is selected then I want sum(Cost).Also, I'd like to use this metric to aggregate on Year/Organisation. Is this possible?Thanks 

3
Joseph Heun
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
C
Chris MacomberParticipating Frequently
asked in Metrics and MAQL

When validating project, getting errors stating "...violates COMPULSORY EDGE..."

Hello,We’ve successfully loaded data from a bigquery, but now I can’t build any insights because as soon as I drag an attribute, the UI throws an error “Connection failed, try again later…”.  We went into the gray pages and ran the “validate-project” process and got a lot of “...violates COMPULSORY EDGE...” errors (see attached logs).  I assume those are the reasons I can’t build insights.  How do I fix those errors? 

3
D
GoodData Employee
2 years ago
A
Anna MNew Participant
asked in Metrics and MAQL

Creating a metric that shows the last previous value generated irregularly

Hello, I'm having trouble creating a CostsPreviousDataset metric that shows me the last previous value for the Costs metric. I managed to set the metric to show the value from the previous day (SELECT Costs FOR PREVIOUS (Date (Datasetdate))), but unfortunately the client does not generate costs once a day every day, but the generation is irregular - it can be once every three days or, conversely, four times a day etc. and the client needs to see a comparison with the previous dataset (not with previous day). Each dataset is marked with a unique integer DatasetID, but I couldn't figure out how to build the formula correctly in MAQL. Thank you for help.

3
Michael Ullock
GoodData Employee
2 years ago
J
jeny.mNew Participant
asked in Metrics and MAQL

Concatenate Column and String

Hello,Can we create a custom column where we concatenate a column and a string? how do we do it? 

3
Moises Morales
GoodData Employee
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
F
FilipNew Participant
asked in Metrics and MAQL

Get SQL behind metrics

Hi all, Is there a way to see what the SQL querying the DB is behind a particular metric? Thanks

3
Jan Kos
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
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
L
LuciaDelloNew Participant
asked in Metrics and MAQL

Is it possible to create an attribute type metric?

Hi, I am trying to create price range bucket to group together products by prices ranges <£50, £50-<£100 in MAQL as a metric then to use it to display results by price ranges.I am able to do the calculation but my new metric is created as a fact not an attribute so I cannot build analysis of different price buckets.I looked at computed metrics too but these only allow me to use pre-created attributes and not fact values within them.Is this possible to do or does this need to happen before the data model enters GooddData. Thanks. 

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