Hi all,
I have a datamodel in which there are seve...
# gooddata-platformh
Hans Cornette
02/23/2024, 12:54 PMHi all,
I have a datamodel in which there are several expense items, that have a link to a Transaction datatable with a Posting Date, and a Report datatable with a Submission Date.
I'm trying to find the difference between each of the dates.
So, I decided to create a DATETIME_DIFF() metric but I don't know what I'm doing wrong.
Can you help?
m
Michael Ullock
02/23/2024, 1:10 PMHi Hans, my colleague Jakub posted this below 🙂
https://gooddataconnect.slack.com/archives/C01USCF4S10/p1708693778418739
h
Hans Cornette
02/23/2024, 1:12 PMThanks both, I'll check it out 🙂
Hans Cornette
02/29/2024, 8:01 AMHi Jakub,
I managed to create the metric as described above.
Next I wanted to display an (empty value) when one of the dates are unavailable.
I tried by applying the where clause, but it seems to remove the records from the query in which either one of the dates are empty.
Can you explain me how I can show an (empty value) when on of the dates are empty?
Hans Cornette
02/29/2024, 8:20 AMAlso when both dates are (empty) this gives a 0.0, can this be amended into an (empty value) ?
Hans Cornette
03/06/2024, 7:57 AMHi @Jakub Sterba, I've implemented the date difference as suggested above, with Report Id being the PK of the Report table.
But, when I do not show the Report Id in the Insight, but its more readable form "Report Reference" the metrics are not available anymore.
I need the dates to be available in the context of that table.
Can you help?
Copy code
SELECT CASE WHEN Date (Settlement Date) = (empty value) THEN -99999, WHEN Date (Control Date) = (empty value) THEN -99999 ELSE (SELECT Date (Settlement Date) - Date (Control Date) BY Report Id) ENDj
Jakub Sterba
03/06/2024, 9:46 AMHi Hans, what you have written is not proper metric which can be used in different contexts (sliced by various attributes such as Report Reference) because it misses aggregation. The metric with BY rule tells that in addition to dimensions used in report difference shall be calculated also on granularity of Report Id. In case Report Id is the only attribute used for breakdown of the report there is no problem. In the report with Report Reference the engine assumes there may be multiple Report Ids for one Report Reference. You can add there some aggregation function like SUM or AVG to tell the engine how to reduce multiple results of date difference to granularity of the report:
Copy code
SELECT CASE WHEN Date (Settlement Date) = (empty value) THEN -99999, WHEN Date (Control Date) = (empty value) THEN -99999 ELSE (SELECT SUM(Date (Settlement Date) - Date (Control Date)) BY Report Id) ENDJakub Sterba
03/06/2024, 9:49 AMor
Copy code
SELECT SUM(SELECT CASE WHEN Date (Settlement Date) = (empty value) THEN -99999, WHEN Date (Control Date) = (empty value) THEN -99999 ELSE (SELECT Date (Settlement Date) - Date (Control Date) BY Report Id) END)Jakub Sterba
03/06/2024, 9:51 AMmaybe following statement will be even better:
Copy code
SELECT MIN(SELECT CASE WHEN Date (Settlement Date) = (empty value) THEN -99999, WHEN Date (Control Date) = (empty value) THEN -99999 ELSE Date (Settlement Date) - Date (Control Date END BY Report Id)Jakub Sterba
03/06/2024, 9:53 AMit should create vector of differences for each report ID or -99999 when date value is (empty value) and minimum will be returned on the granularity of the report
Jakub Sterba
03/06/2024, 9:56 AMyou can also consider using WHERE or HAVING filter to filter out the values. (empty value) will be displayed if such metric is displayed in a table with other metric which has value for row with empty date
Jakub Sterba
03/06/2024, 9:57 AM-99999 can be also formatted as “empty value” using conditional metric format
h
Hans Cornette
03/08/2024, 8:03 AM@Jakub Sterba I will read the above and check it out. Thanks!