Dynamic Rename - Change Headers to Dates

I am using a crosstab tool which sums up volumes by dates. The field headers are coming out as "Sum_YYYY_MM_DD" but I would like them to come out as "DD/MM/YYYY".

I know that I can remove the prefix of "Sum_" by using the dynamic rename tool but does anyone know how I can reorder the date and replace the underscores with forward slashes?

This is how my data currently looks:

Screenshot 2023-09-22 101654.png

Developer Tools

Date Time

Dynamic Processing

Accepted answers

philipmannering

Hey @benmillea.

You should first remove the "Sum_" before the Crosstab Tool. You can do this in a Formula Tool using the Replace() function. This will take care of sorting the dates.

Then use the Dynamic Rename Tool (set to formula) with expression,

DateTimeFormat(DateTimeParse([_CurrentField_], '%Y_%m_%d'), '%d/%m/%Y')

All comments

Peachyco

You can use the Formula Tool to format the dates into "DD/MM/YYYY" before you Cross-Tab them. In this way, your Dynamic Rename Tool only needs to remove the "Sum_" prefix.

philipmannering

Hey @benmillea.

You should first remove the "Sum_" before the Crosstab Tool. You can do this in a Formula Tool using the Replace() function. This will take care of sorting the dates.

Then use the Dynamic Rename Tool (set to formula) with expression,

DateTimeFormat(DateTimeParse([_CurrentField_], '%Y_%m_%d'), '%d/%m/%Y')

benmillea

Thanks for this. It's the crosstab tool that is creating the "Sum_" prefix so I can't remove it before. I've used 2 x dynamic rename tools: the first removes the prefix; and the second one uses the expression that you have provided - this gives me just what I need, so many thanks for your help.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703