Date Formulas

[KMendels]

I have a report with the following columns:

Feb. 2011 Feb. 2010 FY2011 FY2010

The dates are taken from a prompt asking for the Thru date. All those fields are derived from formulas. I would like to create a formula that shows all the months for a given fiscal year. Plus keep the column for FY2011 and the column showing the previous FY.

I want it to look like this (assuming when I'm prompted, I will be choosing FY 2011 as the current FY):

Jul 2011 August 2011...Jun 2011 FY2011 FY2012

I have the code to create a column based on the current month but how what is the code for previous months? FY's run 7.1.10 - 6.30.11 (that would be for FY 2011) so I'll also have to take into consideration the years will be changing at some point.

 

[Madawc]

Take a look at Crosstab. You find it usder Insert

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[lbass]

,Create formulas like these:

//{@FY}:
"FY"+totext(year({table.date}+184),"0000")

//{@CurrYr}:
if {@FY}= "FY"+totext({?FiscalYear},"0000") then
{table.value}

...where {?FiscalYear} is a number parameter with entries like 2012, etc.

Insert a crosstab that uses {table.date} as the column field. While it is selected, click on group options->on change of month. Add your row field (if it exists), and add {@CurrYr} as the summary field. In the customize style tab, check "suppress row totals" and "suppress blank columns" and "suppress blank rows".

Then next to this, insert a second crosstab set up with {@FY} as the column field, {table.value} as the summary, and your row field. Suppress the row label, and go into the customize style tab->format grid lines->uncheck "draw" for each label grid line.

Then in preview mode, move the crosstab so that it aligns with the first crosstab. You can select both and choose format->"align bottoms". Also go into the section expert once you have the desired alignment and check "relative positions".

-LB