Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am using a variable to populate a field with the date which is coming from QVD.
i have written a expression in variable to get the date, but when there are multiple date , i am unable to change the format of the date.
Multiple dates are getting populated using below expression:
Date#(GetFieldSelections([RPT_DT]),'M/D/YYYY') and the format populated in excel is
1/25/2020, 1/26/2020 |
where as desired format is Jan 25, 2020, Jan 26, 2020
when i am trying to achieve this format using below expression
=Date(Date#(GetFieldSelections([RPT_DT]),'M/D/YYYY'),'MMM DD, YYYY')
i am getting - (hyphen)
Please suggest a solution
Thanks!
Hello,
This is working as designed, because the function Date#(FIELD, 'FORMAT'), is suppose to get only one date as string and the it is trying to match it with the format 'M/D/YYYY'. Therefore, when 2+ values are selected the function is trying to match the string '1/25/2020, 1/26/2020' with the string format 'M/D/YYYY' and it is failing so it returns (-) which is Null().
One workaround would be to use this within Data load editor directly:
Data:
Load
Date(Date#([RPT_DT], 'M/D/YYYY'), 'MMM D, YYYY') as RPT_DT
This will make all the values in the dataset with the format you like, so you can then simply populate them as =GetFieldSelections([RPT_DT])
OR:
If you want to keep both date formats, then in Data load editor you can simply do:
Data:
Load
[RPT_DT] as [RPT_DT],
Date(Date#([RPT_DT], 'M/D/YYYY'), 'MMM D, YYYY') as RPT_DT_FORMATED
This will give you 2 fields. So you can make the selections on both fields and use only the RPT_DT_FORMATED for populating the selected values.
I hope that this information was helpful. In case I have misunderstood the use case scenario, please elaborate in details by providing additional information. However, if it has helped you resolve the issue, please mark it as accepted solution to give further visibility to other community members.
Hello,
This is working as designed, because the function Date#(FIELD, 'FORMAT'), is suppose to get only one date as string and the it is trying to match it with the format 'M/D/YYYY'. Therefore, when 2+ values are selected the function is trying to match the string '1/25/2020, 1/26/2020' with the string format 'M/D/YYYY' and it is failing so it returns (-) which is Null().
One workaround would be to use this within Data load editor directly:
Data:
Load
Date(Date#([RPT_DT], 'M/D/YYYY'), 'MMM D, YYYY') as RPT_DT
This will make all the values in the dataset with the format you like, so you can then simply populate them as =GetFieldSelections([RPT_DT])
OR:
If you want to keep both date formats, then in Data load editor you can simply do:
Data:
Load
[RPT_DT] as [RPT_DT],
Date(Date#([RPT_DT], 'M/D/YYYY'), 'MMM D, YYYY') as RPT_DT_FORMATED
This will give you 2 fields. So you can make the selections on both fields and use only the RPT_DT_FORMATED for populating the selected values.
I hope that this information was helpful. In case I have misunderstood the use case scenario, please elaborate in details by providing additional information. However, if it has helped you resolve the issue, please mark it as accepted solution to give further visibility to other community members.