Sort Formula Field in Crystal by date not Alpha

[jince0212]

this is the formula I am using for @column1 - column one is in Cross-tab

if {@Type} = "Q" then if {MV_TRANS_DELPHI_ALLOC_SUMMARY.SOURCE} = '0' then "Allocations"
else
"FY"+{MV_TRANS_DELPHI_ALLOC_SUMMARY.FISCAL_YEAR} + " " +(if month({MV_TRANS_DELPHI_ALLOC_SUMMARY.EXP_DATE}) in [10, 11, 12] then "Q1"
else
if month({MV_TRANS_DELPHI_ALLOC_SUMMARY.EXP_DATE}) in [1, 2, 3] then "Q2"
else
if month({MV_TRANS_DELPHI_ALLOC_SUMMARY.EXP_DATE}) in [4, 5, 6] then "Q3"
else
if month({MV_TRANS_DELPHI_ALLOC_SUMMARY.EXP_DATE}) in [7, 8, 9] then "Q4")

else if {@Type} = "M" then if {MV_TRANS_DELPHI_ALLOC_SUMMARY.SOURCE} = '0' then "Allocations" else totext({MV_TRANS_DELPHI_ALLOC_SUMMARY.EXP_DATE}, "MMM, yyyy")
else if {@Type} = "Y" then if {MV_TRANS_DELPHI_ALLOC_SUMMARY.SOURCE} = '0' then "Allocations" else {MV_TRANS_DELPHI_ALLOC_SUMMARY.FISCAL_YEAR}

When Type = M the data is sorting alphabetically not by date - how d I get it to sort by date?

 

[hilfy]

After looking at the formula, I assume you're having problems in particular with the Monthy type ({@Type?} = 'M') and probably not with the others, correct}

In order to do this, you're going to have to do a couple of things.

1. Modify your formula - change

totext({MV_TRANS_DELPHI_ALLOC_SUMMARY.EXP_DATE}, "MMM, yyyy")

to

totext({MV_TRANS_DELPHI_ALLOC_SUMMARY.EXP_DATE}, "yyyyMM")

This will give you the date in a format that will sort correctly.

2. Right-click on the row name that is using this formula, select Row Options, Group Options, and go to the Options tab.

3. Check "Customize Group Name Field" and select "Use a Formula as Group Name". In the formula put something like this:

If {@Type} = "M" then
({MV_TRANS_DELPHI_ALLOC_SUMMARY.EXP_DATE}, "MMM, yyyy")
else
{@Column1}

This will get you the sort you need as well as the labels that you need.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[jince0212]

thanks you for your quick response - I have done step one.

On step two
If {@Type} = "M" then
({MV_TRANS_DELPHI_ALLOC_SUMMARY.EXP_DATE}, "MMM, yyyy")
else
{@Column1}

I am getting an error The ) is missing error at the comma after the field.

Thanks much

 

[hilfy]

Sorry, I missed something. It should be:

If {@Type} = "M" then
[red]ToText[/red]({MV_TRANS_DELPHI_ALLOC_SUMMARY.EXP_DATE}, "MMM, yyyy")
else
{@Column1}

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[jince0212]

thanks that worked! one more question

step 2
if this is the group formula
if {@Type} = "M" then if {MV_TRANS_DELPHI_ALLOC_SUMMARY.SOURCE} = '0' then "Allocations" else
totext({MV_TRANS_DELPHI_ALLOC_SUMMARY.EXP_DATE}, "MMM, yyyy")
else
{@Column1}

how do I get allocations as first column

thanks for your help

 

[hilfy]

Change "Allocations" to "*Allocations" which should sort it to the top. Then include the logic to use the correct name in the group label formula.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[jince0212]

Perfect!

Thanks