altosaar opened a new issue #14591:
URL: https://github.com/apache/superset/issues/14591
I am a first-time user of Superset. I followed the documentation and used
SQL Lab to create a dataset.
However, when I try to create a virtual calculated column or a metric based
on a SQL query, it is very difficult to know how to do this in SQL Lab.
If I click 'open in SQL Lab' then it will open the SQL Lab editor with the
original SQL query used to generate the dataset.
For example, one of the columns I have is a datetime for birth, and I need
to create a calculated column (or metric?) for the age, in decimals. This is
pretty tricky, and is a multi-line SQL function:
```
DECLARE @Now datetime, @Dob datetime
SELECT @Now='1990-05-05', @Dob='1980-05-05' --results in 10.000000000000
--SELECT @Now='1990-05-04', @Dob='1980-05-05' --results in 9.997260273973
--SELECT @Now='1989-05-06', @Dob='1980-05-05' --results in 9.002739726027
--SELECT @Now='1990-05-06', @Dob='1980-05-05' --results in 10.002739726027
--SELECT @Now='1990-12-06', @Dob='1980-05-05' --results in 10.589041095890
--SELECT @Now='1991-05-04', @Dob='1980-05-05' --results in 10.997260273973
SELECT 1.0* DateDiff(yy,@Dob,@Now)
+CASE
WHEN @Now >=
DATEFROMPARTS(DATEPART(yyyy,@Now),DATEPART(m,@Dob),DATEPART(d,@Dob)) THEN
--birthday has happened for the @now year, so add some portion onto the year
difference
( 1.0 --force automatic conversions from int to decimal
*
DATEDIFF(day,DATEFROMPARTS(DATEPART(yyyy,@Now),DATEPART(m,@Dob),DATEPART(d,@Dob)),@Now)
--number of days difference between the @Now year birthday and the @Now day
/
DATEDIFF(day,DATEFROMPARTS(DATEPART(yyyy,@Now),1,1),DATEFROMPARTS(DATEPART(yyyy,@Now)+1,1,1))
--number of days in the @Now year
)
ELSE --birthday has not been reached for the last year, so remove
some portion of the year difference
-1 --remove this fractional difference onto the age
* ( -1.0 --force automatic conversions from int to decimal
*
DATEDIFF(day,DATEFROMPARTS(DATEPART(yyyy,@Now),DATEPART(m,@Dob),DATEPART(d,@Dob)),@Now)
--number of days difference between the @Now year birthday and the @Now day
/
DATEDIFF(day,DATEFROMPARTS(DATEPART(yyyy,@Now),1,1),DATEFROMPARTS(DATEPART(yyyy,@Now)+1,1,1))
--number of days in the @Now year
)
END AS AgeYearsDecimal
```
(from
https://stackoverflow.com/questions/1572110/how-to-calculate-age-in-years-based-on-date-of-birth-and-getdate)
How might I edit this function in SQL Lab so it either becomes a column,
virtual calculated column, or metric for a dataset?
The closest documentation resources for this type of workflow I've found:
* https://superset.apache.org/docs/creating-charts-dashboards/first-dashboard
*
https://openlmis.atlassian.net/wiki/spaces/OP/pages/231506016/Superset+Tutorial
Thanks so much for this awesome project! Just wanted to point out where a
first-time user might get lost when following the 'first dashboard' tutorial.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
