Sorting dymanic date columns

[Sohdaw]

Hi,

I have a workflow where i have independently set a formula for the date from T-1 to T+5, then use a join tool to look up the date in my dataset. I also have a sum tool then cross tab it to present it in columns. 

Account number	Sum of T-1	Sum of T	Sum of T+1	Sum of T+2	Sum of T+3	Sum of T+4	Sum of T+5
abc	-12345	-12345	-12345	-12345	-12345	-12345	-12345
erf	567	567	567	567	567	567	567
sdf	246	246	246	246	246	246	246

How do i ensure that the dates will always come up in this format of T-1 to T+5? In the past few days given that it has crossed from Mar to Apr, my date columns are coming up as eg. Sum of T+4, Sum of T+5, Sum of T-1, Sum of T, Sum of T+1, Sum of T+2, Sum of T+3. 

I have tried using a sort tool before the cross tab to sort the date, which i have as a separate column to the string (eg. sum of T), but once the cross tab tool hits the date columns are still in jumbled up format.

Thanks 

[martinding]

Hi @Sohdaw, 

1. (After sorting the dates) Try adding a Record ID Tool and use a Formula Tool to concatenate the Record ID's value with your Date (both as strings).

2. So something like: ToString([RecordID])+ "_" + ToString([Date])

3. Then we you do the Cross Tab, it will go in order from the smallest to the last (based on the value of the Record ID that appears in front).

You can use a dynamic rename tool in the end to format the column names to your liking.