Crystal Display Monthly totals in side by side bar chart with rolling first month

[mrodonis]

Hi,

I am attempting to create a side by side bar chart in Crystal to display monthly totals for 2 years of data, displaying the data monthly with Year 1 and Year 2 totals side by side in the chart. I would like the first month to be defined by the Month/Year that a user enters into a parameter and displays the 11 following months from there. So for example, If I enter April/2013, The first month in the bar chart is April, and the last month displayed is May. Each Month shows a bar with Year 1 and Year 2 totals... Year 2 is 2013 For Jan-April and 2012 for May-Dec. Year 1 is 2012 for Jan- Apr and 2011 for May -Dec.
I have used these formulas
Month:
if month({date})<10 then
"0"+totext(month(date}),0)
else
totext(month({date),0)
Date:
(totext(year({date}),0,"")+" - "+ {@month})
to order the data, but it creates a chart with a single column for each month, I want a yearly chart with side by side columns for each month.
The big question is how do I define the rolling months, so that the Month/Year entered is the first month and the rest follow from there?
Hope this is clear.
Help is much appreciated.
Michelle

 

[Madawc]

For rolling months, you use DateAdd from currentdate. Or from a parameter date.

You could use it for selection, it could be something like

{data.date} >= DateAdd("m", -11, @Parameter.Date)

Put that in record selection and it ignores everything earlier.

Then group by calendar month. Let Crystal do the work.

I don't do many graphs but I think you can easily develop a graph from that.

PS. If you need the earlier data for something else, you might need to use a subreport. Use Edit > Subreport Links to feed it your parameter.

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

 

[mrodonis]

Hi, thanks for responding. My issue is not getting the data, it is in displaying the data in a chart they way I need it to display. I need to show each month with a Year 1 and Year 2 value (side by side). The problem is getting the months to display in order based in a rolling year based on a date parameter.

 

[mrodonis]

Link

Sorry, the previous attached file did not work. This link should. Notice the Months displayed have an alpha character displayed. What I would prefer to see is just the Month abbreviation, but I can't figure out how to sort it correctly and display only the Month abbr.

 

[Madawc]

Sorry, no idea. Can anyone else help?

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

 

[Charliy]

Just insert chart, Type Side by Side bar, On change of Year, Month

You'll need formulas to get just the year and month from your date field, but that's easy.