How to edit metric in order to get the latest date?

Question

I'm trying to get the latest Date group by an attribute in mm/dd/yyyy format. My code is:

select MAX(Date (Ticket Created Date)) BY EntityID

I've already tried to edit “Metric format” to “mm/dd/yyyy” format, but I wasn't successful. Do you have any better idea how to do it? Thanks

Answer 1

Your metric returns a number (id of the day), not the date in the mm/dd/yyyy format. You can use a custom number formatting to change the display in the numeric expression. Detailed information can be found in the documentation.

There you'll find the following example

SELECT MAX ((SELECT (10000 * MAX(Year (Date)))
                    + (100 * MAX(Month (Date)))
                    + (MAX(Day of Month (Date))) BY <connection point>))
WHERE (SELECT COUNT(Date (Date), <connection point>) BY Year (Date), ALL OTHER) > 0

This shows the dates in the yyyymmdd format, so you need to tweak it a bit to create mm/dd/yyyy:

SELECT (1000000 * MAX(Month (Date))
        + 10000 * MAX(Day of Month (Date))
        + MAX(Year (Date)))
WHERE ...

You can then set the format to 00/00/0000. But note that sorting this value is not easy, unlike the yyyymmdd.