Trying to show the last session date for each learner. I have followed this proposal to create a metric based on a date from 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
I use :
SELECT MAX ((SELECT (100000 * MAX(Day of Month (Timestamp))) + (100 * MAX(Month (Timestamp))) + (MAX(Year (Timestamp))-2022+2022) BY Coaching Session Uuid)) WHERE (SELECT COUNT(Date (Timestamp), Coaching Session Uuid) BY Year (Timestamp), ALL OTHER) > 0
and 0#/0#/#### (day/month.year format) for the format but I get crazy dates. Not sure what are these 1000000 and 100 in the metric.
Best answer by Moises MoralesView original
Hi Jean-Christophe, it might be possible that you are using a different/outdated date template and there is a workaround to make the metric format show the correct date format, this is covered in this Community post here: How can I custom a attribute or metric in Date format? | The GoodData Community. Please check the message from my colleague Jan and you can also check the rest of the post for further hints. I hope this helps.
I already checked that. I use the custom_v2:date template reason why I do a -2022+2022.
But in my case even day and month are incorrect. probably due to the 10000 and 100 ? not sure what is it used for.
As per the workaround, you would need to make sure that you are following the correct arithmetic operations, this is because dates in GoodData platform work different from facts:
Thanks for you support. I have understood the calculation and this is working now.