Metrics and MAQL
Metrics and MAQL
- 100 Topics
- 328 Replies
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!
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
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
Hi I have a fact that can be Null at times.I can count the records where it is not nullselect count(attribute) where fact > 0.I am trying to count the amount of records that are null.I do not see anything in the MAQL guide for that. Is there a way?I tried fact = 0 even know I know null is not 0.I tried fact <=0 Neither worked.
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
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
We have a table with several records where we need to perform the moving average of the last 90 days considering only grouping by one column (fluxo_id_card).If we don't consider the rolling avarage, the calculation is performed as follows:But what happens is that we need to display these results in a month-to-month progression, where each month we view the average of the last 90 days.For the Rolling average, the basic syntax is:But we don’t have a certain number of lines that each card would occupy, and that Rows Between does not currently support the COUNT function.How can we adapt this calculation to the new context without changing the original table in the model?
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 :)
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.
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
Guys, how are you? I'm trying to create a column that shows the percentage of each item's quantity based on the total sum of quantity. I'm not getting success... if I put the percentage directly in the insight, I can't add another column with integer values. Is there any metric that brings me these values?Attempt to create a metric...SELECT (SELECT SUM(Quantidade) BY Tenant, ALL OTHER WITHOUT PF) / (SELECT SUM(Quantidade))Trying through Analyze- This is the desired column - But when adding the quantity column in integer format, it disables the "show in %" option
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
Hi guys,I have request for creating metrics with the formatting like HH:MM or MM:SS or HH:MM:SS like the image above. My metric will return a decimal number and then I must format it in the time format. Assume that my metric result is a number of seconds, I think that maybe I will divide the number by 86400.0 (the number of seconds in a day), 3600.0 (the number of minutes in a day) and 60 (the number of hours in a day).This is the first time I’ve formatted metric related to time format, so I don’t have any idea about it. Do you have any better idea?Thanks for your help!
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.
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
Hello,I have created a simple metric (just a simple logic between attribute and fact). The Metric is available in the `Analyze` section along other facts and attributes.However if I try to construct f.e. `Line chart` and drag this metric to `Metrics` section, all other attributes disappear and I’m unable to use them (f.e. I’m unable to `Trend by` Date, etc...). Also, if I first try to `Trend by` Date, the added metric disappears from the available data and I see the warning `1 unrelated data item hidden`. Obviously, somehow the metrics are not connected with other data - how can I please achieve that? Haven’t found any relevant posts about that in the docs nor the community…Thanks.
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.
Already have an account? Login
Login to the community
Sign in with your community account. This is different than your login to your GoodData account. Don't have a community login? Create an account
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.