Solved

Average age from date of birth

  • 12 July 2023
  • 6 replies
  • 68 views

I am trying to find the average age of customers from date of birth but struggling. Any pointers would be appriecated. I have looked at the following two webpages but still just keep getting errors. I imagine it will look something like this. 

SELECT AVG(SELECT Date({metric/this_year}) - {label/DateOfBirth.year} )

 

https://help.gooddata.com/doc/enterprise/en/dashboards-and-insights/maql-analytical-query-language/maql-use-cases-and-tutorials/date-attribute-arithmetic/

icon

Best answer by michal 20 July 2023, 18:24

View original

6 replies

great thank you. it is working now.

Userlevel 2

Hi James, please try this code, it should work. 

SELECT AVG(
(SELECT
DATETIME_DIFF( {DateOfBirth - Year{label/DateOfBirth.year}, THIS(YEAR) )
BY {dataset/your_dataset}
)
)

the last error message is complaining about missing aggregation so we need to wrap the function to AVG - you had it in your original code.

And I also added the “BY dataset” part. It might not be needed in insights with the primary key from the dataset where the date exists, but it might not work if you would just use it alone (i.e. to display AVG age as a headline) or some other attribute.

 

The “BY dataset” in the inner metric - in your case please use the identifier of the dataset in your model - will ensure that this metric can be also used alone or with non-key attribute. Technically any Date in GoodData is a separate dimension dataset which can be referenced from other datasets, so by this we are telling the system to use the date in connection with your dataset.

Hey

Still no joy.

 

Userlevel 3

Could you firstly try to get a result of a simple metric similar to my example below, please?

 

SELECT DATETIME_DIFF({DateOfBirth - Year{label/DateOfBirth.year}, THIS(YEAR))

 

Hi, thanks for your response. I am still running into some issues.

 

 

Userlevel 3

Hi James,

Looks like you are working with GoodData Cloud, however, both linked articles refers to reporting in GoodData Platform.

Please be so kind and check the Cloud documentation below:

DATETIME_DIFF

AVG

Please let me know how does it work for you.

Reply