Grouping by Year

[Storyteller]

Hello All,

I have investments that have transactions {InvestTrans.EffectDt} that go from 1996 to 2001 and I would like to group {InvestTrans.EffectDt} every year on the change of {CorpUserDef.Date3}. However {CorpUserDef.Date3} has only one value. For exapmle {CorpUserDef.Date3} = 2001/2/28 12:00AM.


I would like to create a formula that would allow me to group by
Year({CorpuserDef.Date3}) then
Year({CorpuserDef.Date3}-1) then
Year({CorpuserDef.Date3}-2) then
Year({CorpuserDef.Date3}-3) then
Year({CorpuserDef.Date3}-4)

I think I'm on the right track with my line of thought but I have not been able to get it to work.

Any help would be greatly appreciated.

Thanks,
Michael

 

[dgillz]

Create a formula like this: @Year=year({Date/timefield})

That formula will return a 4 digit integer for the year. Group by that field.

You might also consider a cross tab report with any date field as a column and select crosstab group options, create a new column for each year. The upside to Crosstabs is they are extremely quick and easy, the downside is limited modifications and no drill down.

Let me know if you have questions. Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[Storyteller]

Hello dgillz,

Thank you for your advice. however it return 2001 for all groups.

We have had some luck with the following:
If (Month({InvestTrans.EffectDt})>Month({?Fiscal Year}))
Then
Date(Year({InvestTrans.EffectDt}),Month({?Fiscal Year}),Day({?Fiscal Year}))
Else
Date(Year({InvestTrans.EffectDt})-1,Month({?Fiscal Year}),Day({?Fiscal Year}))

I'm just testing it against a number of dates.

Thanks,
Michael

 

[dgillz]

The only way my formula could return 2001 for every record is if every record on your report was dated 2001. Are you using a record selection formula that restricts what the report is looking at by date?
Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[Storyteller]

Hello dgillz,

Your suggestion would work if I wanted to group by Transaction Date {InvestTrans.EffectDt}. However I need to group by Fiscal Year End {CorpUserDef.Date3}. The problem is that I only have one value for the Fiscal Year End. For example 2001/02/28 and I want to group {InvestTrans.EffectDt} that occured in 1995, 1996, 1997, 1998, 1999, 2000 Fiscal Year Ends.

Michael

 

[dgillz]

You are not reading my posts very closely, nothing I have suggested will give you one group per date, it will only give you one group per year, namely everything with a date from 01/01/YYYY to 12/31/YYYY.

Are you saying that you want to group everything within a fiscal year not a calendar year, and that your fiscal year end is 2/28/YYYY?

If so, then create a formulas:

@1995
if {Datefield} in Date(1994,03,01) to Date(1995,02,28) then {transactionfield} else 0

@1995
if {Datefield} in Date(1994,03,01) to Date(1995,02,28) then {transactionfield} else 0

@1996
if {Datefield} in Date(1995,03,01) to Date(1996,02,29) then {transactionfield} else 0

@1997
if {Datefield} in Date(1996,03,01) to Date(1997,02,28) then {transactionfield} else 0

@1998
if {Datefield} in Date(1997,03,01) to Date(1998,02,28) then {transactionfield} else 0

etc..etc...

Subtotal/Grandtotal these fields however you need to.




Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office