v8.5-Calculate date and use in Group 1

[markajem]

I am presently using a formula to calculate -45 days from a date and using this formula to create the Group and formatting the group to show mmm-yy. My problem is that it is sorting the group by acsending order instead of by month order. The numbers are coming out correctly. I do not have the option thru "Change Group Option" to sort by each month, each day etc. as you would normally if you used a date field for the group.

I also tried to use "In specified order" in "Change Group Option" but really dont know where to go from there even if that will solve the problem.

if {POP10110.PRMDATE} <> CDateTime (1900, 01, 01, 00, 00, 00)
    then (totext(DateAdd ("d", -44, {POP10110.PRMDATE}),"MMM-yy"))
        else "None"

So now my data is coming out like:
[tt]
Date Amount
------- --------
Apr-06 763.99
Feb-06 234.29
Jul-06 223.23
Jun-06 136.54
May-06 232.87
[/tt]

Need to be:
[tt]
Date Amount
------- --------
Feb-06 234.29
Apr-06 763.99
May-06 232.87
Jun-06 136.54
Jul-06 223.23
[/tt]

Any ideas?

Thanks

 

[synapsevampire]

You should be using a date field for the group (set it to group by month), and then formatting the display by right clicking the field and using the format field->Date and select that format ratehr than creating a formula to group by.

Or use your current formula for display purposes if you want to show NONE.

-k

 

[markajem]

Confused! I am using a calculation for the date. I can't use the date field because it will not be correct. I am going back 45 days from the date field; so therefore the month will be back one month. When doing so I do not have the option to Group by month, day etc. It only Groups (sorts) alphabetically in acsending order and not by month; Jan, Feb, Mar etc.

Understand; if the date field says Apr-15,2006 I am going back 45 days. If I use the date field I get April 15,2006 instead of Mar 01, 2006. The totals appear in April instead of March but the sort is by alpha.

Hope that clarifies better.

 

[synapsevampire]

I should have said A date field, through a formula, as in:

if {POP10110.PRMDATE} <> CDateTime (1900, 01, 01, 00, 00, 00)
then {POP10110.PRMDATE}-44
else
cdate(1970,1,1)

Resolve the 1/1/1970's in a display formula.

Not sure why you want the ones that aren't in that range in the report anyway, I'd eliminate them in the record selection formula.

-k

 

[markajem]

That did it.

Thanks for your help.