Filip Charikov
09/26/2023, 1:58 PMSELECT 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:
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.Francisco Antunes
09/26/2023, 2:48 PMThe results cannot be displayed in visualizations directly, but they can be used in conditional filteringThe article has some examples that will help you understand how to better use it, like the one below:
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).Filip Charikov
09/26/2023, 3:35 PMSELECT {label/imported_date.day}
WHERE {label/imported_date.day} = (SELECT MIN({label/imported_date.day},{dataset/data}))
Francisco Antunes
09/26/2023, 4:28 PMSELECT (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:
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!