Date Display on Dashboard Hack

by Milan Veverka

In AQE, as you know, you can do mathematical operations over the date dimension attributes. That can be used to get "datepart" like functions:

  • MAX(Year (date)) gives you actual year (like 2014)
  • MAX(month/year (date)) gives you numbers 1-12

Possible uses and implications:
  • You can turn a date into metric. For example: MAX(Year)*100 + MAX(month/year) gives you something like 201402, which, with a metric custom format ####-## can be displayed as 2014-02
When filtering, don't forget that the filter on the dashboard reliably filters only the Date attribute. So, for example if you want to show the year of the last date selected on a DDD filter, you will need to write:

SELECT MAX(year (ddd) ) WHERE (SELECT COUNT(Date (ddd) ) BY year (ddd), ALL OTHER > 0 )

(think filtering by child table here)

Some "Date Arithmetics" GoodData Articles: