What am I doing wrong? I want to find out the min ...
# gooddata-cloud
What am I doing wrong? I want to find out the min imported data date from each office. I made MAQL using this
SELECT MIN({label/imported_date.day}) USING {dataset/data}
but when I drag it in, I lose all my attribute (like the aforementioned office) fields. It also displays an error:
Top level metric return type=date not allowed
. Essentially I want to create something like if this was SQL:
Copy code
SELECT office, min(imported_date), max(imported_date)
from data
GROUP BY office
My entire LDM is a single table, with 9 date attributes to its left. Nothing else.
Hi Filip, as the article about the MIN function explains, when used on attributes with non-numerical data types (such as Date, as is the case here)
The results cannot be displayed in visualizations directly, but they can be used in conditional filtering
The article has some examples that will help you understand how to better use it, like the one below:
Copy code
SELECT SUM({fact/snapshot_value}) 
WHERE {attribute/snapshot_date.day} =
      (SELECT MIN({attribute/snapshot_date.day},{dataset/fact_dataset}))
Note that the MIN function is used after a WHERE conditional. It cannot be the top-level metric (which is why you got the error).
@Francisco Antunes The formula you put is not what i'm looking for: I don't want a sum of some other value based on min date, I want the min date itself to display, and unfortunately this doesn't seem to work.
Copy code
SELECT {label/imported_date.day}
WHERE {label/imported_date.day} = (SELECT MIN({label/imported_date.day},{dataset/data}))
Hi Filip, As the MAQL article explains, metrics must always return a numerical value. Unlike SQL, you cannot perform a SELECT statement that will return you non-numerical values (such as a
SELECT (date)
-type metric). To get the kind of table you are looking for, you would need to have a function on the top-level of the metric that returns a numerical value (like a SUM, or COUNT), and then use a conditional WHERE for the MIN date. Then, you’d add the Date dimension and the Office attribute to the Row/Column section in the Analytical Designer, to ensure the resulting visualization will slice the metric by them. Here’s a silly example, to illustrate. You mentioned wanting to find out the minimum imported data date. So you’d start by getting, say, the COUNT of imported data first:
Copy code
SELECT COUNT({label/imported_data}) WHERE {label/imported_date.day} = (SELECT MIN({label/imported_date.day},{dataset/data}))
The result is a number: the count of Imported Data with the earliest possible date. When you add that as the metric and slice it by the other criteria, you’d get a table showing the count of imported data, the office where it occurred, and the Date (which, in this case, will be the Minimum date, because that’s the condition for the sum to be calculated) when it occurred. The challenge is finding the best way to calculate/present this metric, but that will depend on your data model. Hope this helps!