Cross Tab Date

[whiteninja]

Trying to create the following:

Jan Feb Mar
Item Sales Volume Std Sales Volume Std Sales Volume Std

ABC 10 5 4 9 7 3 8 6 4



How do I create a Date column for past 3 years.
Any help appreciated. Thanks.

 

[Madawc]

If you start making the crosstab, you'll get the option to group by monhts. And you can show three fields, though not next to each other.

If you want the exact form, you'll need to make a mock-crosstab.

A 'Mock Crosstab' is something that looks like a Crosstab, but in fact you define each column yourself, normally as a running total. This would need to go in the report footer, because running totals count as the reports 'run' and they will not be complete until then. Crystal should have included an example along with the Crosstabs.

You can save a little time by doing a paste to a dummy report, changing the name and then pasting back. In Crystal 11.5, you can also duplicate formula fields using the Field Explorer.

It always helps to give your Crystal version - 8, 8.5, 9, 10, 11 or whatever. Methods sometimes change between versions, and higher versions have extra options.


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

 

[lbass]

Madawc,

In versions later than 8.5, there is an option for horizontal display of summaries in the customize style tab, so it is possible.

whiteninja,

You need to use a record selection formula that limits records to the last three years, as in:

{table.date} in date(year(currentdate)-2,1,1) to date(year(currentdate), 12, 31)

Then add {table.date} as your column field->options->print on change of month. You can then format the label by right clicking on it->format field->date and select the desired display either there or in the customize tab.

-LB

 

[whiteninja]

Using Crystal 11 - I used the horizontal display option for summarize fields and grouped by month - used parameter to limit 3 years

 

[lbass]

And what happened? How did you "group by month"? What is your record selection fomrula?

-LB

 

[whiteninja]

Used Cross Tab and grouped by month - then used select expert and set it for date between last 3 yrs.

 

[lbass]

Please explain how the results differed from what you expected, as I can't tell what the issue is.

-LB