asked on

Need Year over Year/ month by month Customer Count CHART in RS...?

Experts:

RS Newbie. I need to know HOW to tell a simple BAR CHART to show my Customer COUNTS by month...and also...compare a BAR for Year over Year Comparison IF the user chooses more than one year...?

I have a simple table like this:

CUSTID, ORDERDATE

I need a bar to show total COUNS for each month....and IF more than one year is chosen, the have a secondary bar....side by side in the MONTH.

Make sense...I"m sure its easy to do,..I'm just new to RS.

Thanks
M

Kevin Cross

A simple method using the query for data itself would be to split data into year/month or combination thereof to use in charts:

Select Year(ORDERDATE), Month(ORDERDATE), COUNT(CUSTID)
From Orders
Group By Year(ORDERDATE), Month(ORDERDATE)

Open in new window

Kevin Cross

Can also concentate year and month to make a unique period indicator like:

Select Str(Year(ORDERDATE)) + '-' + Str(Month(ORDERDATE)), Count(CUSTID)
From Orders
Group By Year(ORDERDATE), Month(ORDERDATE)
Order By 1
 
-- Now charts will have 2007-01 --> 10 and 2008-01 --> 12 for comparison

Open in new window

Marcus Aurelius

ASKER

Oh ok..so you have to do the YEAR in the SQL...???

right now,...I'm able to separate the MONTH in the RS Chart options...why can't I do the same for the year...?

Or is your way just the easiest...?

thanks
M

Marcus Aurelius

ASKER

I'm using something like:

=FORMAT(!fields.orderdate.value,"yy-mm")

or something like this...and this gives me each month separated on a bar...I need the YEAR to be separated and compared....to each other..

Michael Krumpe

You could create a view to format your fields and data - then query against that view with the group-by statements suggested.

Marcus Aurelius

ASKER

OK...so HOW do I set this up in the actual CHART...what goes where....?

Thanks
Mike

ASKER CERTIFIED SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial