Solved

How can I custom a attribute or metric in Date format?

  • 10 March 2021
  • 9 replies
  • 839 views

  • Known Participant
  • 11 replies

Hi,

I want to get the latest Date group by a attribute in mm/dd/yyyy format.

My code is :

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

And I edit “Metric format” to “mm/dd/yyyy” format, but it doesn’t work.

Do you have any better idea?

Thanks for your help!

icon

Best answer by Michael Ullock 15 March 2021, 14:23

View original

9 replies

Userlevel 2

Hi Lena, 

if you're creating insights via Analytical Designer - all the insights already display the date in the following format: MM/DD/YYYY. More details can found in the below documentation: 
https://help.gooddata.com/doc/en/dashboards-and-insights/analytical-designer/visualize-your-data/create-insights

Alternatively, if you're creating reports via the Report Editor as outlined in the below documentation: 
https://help.gooddata.com/doc/en/dashboards-and-insights/reports/working-with-reports/creating-new-reports-in-the-report-editor
 

You have 2 options to change the display labels: in the report and you have two choices:

1) In table mode, right-click on the date header and select a new "display label" from the menu

2) Otherwise, under "HOW" dialog box in your report, select your date dimension, and in the third column, select the most appropriate "display label". 

Hope this helps! 

Hi Michael,

In my case I have a Entity that has 4 Tickets were created in different days, the report displays the latest created Ticket date and the latest Close date for each Entity.

Example: My Entity has 4 tickets something like that

Ticket #                     Created Date                     Closed Date

TK#1                        01/12/2021                         01/20/2021

TK#2                       01/13/2021                         01/21/2021

TK#3                        01/14/2021                         01/22/2021

TK#4                       01/15/2021                         01/23/2021

 

I would like to display on the report:

Entity ID                    Ticket Created Date                         Ticket Closed Date

0101                        01/15/2021                                          01/23/2021                    

 

So I custom the metric to get latest date for each Entity ID, but it is not working

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

 

 

Userlevel 2

Hi Lena, 

my apologies for not initially understanding the issue here - please correct me if I am wrong, the issue here is that your metric returns a number, not the date in this format: mm/dd/yyyy. You can use a custom number formating to change the display in the numeric expression. Please see the following documentation that will help you with this:
https://help.gooddata.com/doc/en/dashboards-and-insights/maql-analytical-query-language/maql-use-cases-and-tutorials/finding-min-and-max-dates-using-date-arithmetic

In the above documentation, we have the following example - you'll need to slightly tweak this for your usecase of: mm/dd/yyyy

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

 

Hi Michael,

Thanks for your support, although it was a bit difficult to do but finally it works correctly now.

MAQL

 

Metric Format: 0#/0#/####

Result:

 

 

 

Thanks,

Lena

Hi Michael,

I had the issue which can not use multiple Date dimensions in one Insight, I found that this issue was fixed today, please following this link: Cannot pull multiple Date dimensions into one Insight | The GoodData Community

 

Currently, there is still another issue, I cannot use this formula: Status DIQ: select THIS - “WO Closed Date”,   because the “WO Closed Date” haven’t included in the Insight.

Example for another formula I can use: Tickets DIQ: select THIS - “Ticket Created Date”, this formula is usable because “Ticket Created Date” column is already included.

Could you please take a look on this? 

 

Thanks!

 

Userlevel 2

Hi Lena, 

as this issue was discussed and resolved outside of this thread - I just wanted to follow up here. 


After looking at your model, I added the primary key/connection point to your report/insight - After adding the primary key to your report I was able to add your metric into your report, which resolved this issue. The direction of the arrow determines which dataset's data can be analyzed (sliced) by the data from the other dataset. For more details on the Primary Key/Connection Point - please see the following documentation:
https://help.gooddata.com/doc/enterprise/en/data-integration/data-modeling-in-gooddata/logical-data-model-components-in-gooddata/connection-points-in-logical-data-models

https://help.gooddata.com/doc/enterprise/en/data-integration/data-preparation-and-distribution/data-preparation-and-distribution-pipeline/data-pipeline-reference/data-warehouse-reference/data-warehouse/database-schema-design/logical-schema-design-tables-and-views/primary-and-foreign-keys

 

Userlevel 3

Hello,

Based upon a recent feedback from a customer, I would like to highlight the workaround that can be used for displaying the year.

As described in: https://help.gooddata.com/doc/enterprise/en/dashboards-and-insights/manage-custom-date-dimensions

Comparing to the urn:gooddata:date date dimension, the urn:custom_v2:date date dimension uses a different method of calculating date aggregations.

For example, a metric returning the latest year (such as SELECT MAX(Year)) would return the ID of the attribute value corresponding to the year, not the actual year (for instance, 121 instead of 2020).

With the workaround:

If you need to display the actual year, update the metric:

Subtract a numeric value that equals the ID of the attribute value for the year. To do so, select the corresponding year from Attribute Values in the right-hand side menu. For information about editing a metric in the Custom Metric Editor, see Get Started with Using MAQL to Write Metrics.

Add a numeric value that equals the year. For example, for the year of 2020, the updated metric would look like the following:

SELECT MAX(Year (Date)) - 2020 + 2020

 

In other words, if your data template is urn:custom_v2:date, the following will not work for displaying the year:

SELECT MAX(Year (Date))

The following will work:

SELECT MAX(Year (Date)) - 2020 + 2020

The number marked in bold needs to be selected from the list of attribute values, not typed manually. The other option would be to convert the date template back to urn:gooddata:date.

Hi Lena, 

my apologies for not initially understanding the issue here - please correct me if I am wrong, the issue here is that your metric returns a number, not the date in this format: mm/dd/yyyy. You can use a custom number formating to change the display in the numeric expression. Please see the following documentation that will help you with this:
https://help.gooddata.com/doc/en/dashboards-and-insights/maql-analytical-query-language/maql-use-cases-and-tutorials/finding-min-and-max-dates-using-date-arithmetic

In the above documentation, we have the following example - you'll need to slightly tweak this for your usecase of: mm/dd/yyyy

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 article no longer exists, do you have an updated link for this article?

Userlevel 2

@tylervaleo you can find the documentation under the bellow URL as it was moved to our classic section:
https://help.gooddata.com/classic/en/dashboards-and-insights/maql-analytical-query-language/maql-use-cases-and-tutorials/finding-min-and-max-dates-using-date-arithmetic

Reply