We have a customfield in projectrack (formerly Profields) that is a string used to store date due to synchronisation process with external tool.
We need to use this field as a Measure date on Eazybi in order to display a Gantt.
The current format is “dd.mm-yyyy”, for instance “01.05.2021” stands for 1st of may 2021.
I was trying to create a custom measure, using dateParse on the existing string measure. but is not working as expected.
Do you have any advice on how we could parse this string in Date ?
Thanks a lot.
Function DateParse() expects date in specific format,
mmm dd yyyy.
You might want to decompose and compose the string to match
yyyy-dd-mm format and then convert it to the date type. Use string transformation functions Right() and ExtractString() and concatenation || for rearranging order of parts of string
--reorganize string to match format yyyy-mm-dd
--year is last 4 values
Right([Measures].[string date format],4) ||"-"||
--month is first group of numbers after dot
ExtractString([Measures].[string date format],'.*\.(\d+).*',1) ||"-"||
--day is first group of numbers
ExtractString([Measures].[string date format],"(\d+)",1)
After those transformations, you may set measure formatting to Date / Time.
More details on used functions described in the documentation: MDX function reference.
Zane / support@eazyBI.com
I had to change the second part for extracting the months.
But this is definitevely what I was looking for.
Thanks a lot !