I have date in dd-mm-yyyy format. I want to add a new column to convert the date in mmm-yy which should show the month and year. Also this should be in date format so that it should appear in chronological order in Crosstab (not in alphabetical order).
Can someone help me please. Thanks
Solved! Go to Solution.
You can convert the date to that format with a formula like this:
Datetimeformat(DateTimeParse([date],'%d-%m-%Y'),'%b-%y')
but you can only save it as a Text datatype. Data datatypes have to have the standard date format
If you can post some sample data, we can help you with the Crosstab issue. I think I understand the problem, but an example would make it easier to help.
Hi @mohit9garg,
as I was already working with some DateTime data, I thought I'd mock up a workflow for you:
Input-Format:
What happens:
- Parsing the date in two new formats (Formula)
- Order them with the help of DateTime-object (Sort) - This is important for the ID generated afterwards
- I generate an ID based on the Formated Date field to help keep them in order after cross tab (Multi-Row-Formula)
- ID and Formated Date get combined (Formula)
- We shift the data (Cross Tab)
- We fix the column names (Dynamic Rename)
Result looks like this:
I'll attach the sample workflow. You'll probably have a Group By column (or multiple) in your real data and have to change this, but the workflow should give you an idea how to do it. Let me know what you think.
Best
Alex
Thanks for your help Alex.
However this solution is working if I am taking month in 'Column Header'. What if I want to take Month in 'Group Data' in Crosstab.
Regards,
Mohit
Hi @mohit9garg,
can you post the format of your desired output? This will make it a lot easier to understand.
Best
Alex
We should have went this route earlier 😃
You can do this using a Formula and Multi-Field-Formula.
The Formula cleans up the Month Column with a Regex-Replace function:
Regex_Replace([Month],'(^\d+\s)','')
This will only replace the first number (no matter if one or two digits) and the space in front of the month name.
I am choosing a Multi-Field-Formula for the next one, because we don't want to add the same formula over and over. This is also dynamic for more columns in the future.
ToString([_CurrentField_]) + '%'
The workflow is attached. Let me know what you think.
Best
Alex