Solved

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

  • 10 March 2021
  • 6 replies
  • 99 views

  • Participating Frequently
  • 5 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

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

 

View original

6 replies

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

 

 

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!

 

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

 

Reply