Graph - How Do I???

Clif
Clif used Ask the Experts™
on
I need a graph similar to this:
ExampleObviously there are eight columns, two for each quarter.  Each of the two columns for each quarter represents two years (2011 and 2012).  Each column represents two values, Income (yellow) and Expense (Cyan)

In the Chart Expert, I select 'For each record' to be year and 'Show Values' of Income and Expense.

I can get a single set of two columns (any particular quarter), but how do I get all quarters to show up as in the example graph?

My query currently returns Year, Value Type (I = income, E = expense) and the values for each month which I calculate to give me the values per quarter.  So, I might have two records that return values such as this:
Year   Type   January   February   March     April
2011     I    600000    650000     500000    450000
2012     I    800000	900000     550000    600000
2011     E    800000	900000     900000    1000000
2012     E    900000	1000000    14000000  1000000

Open in new window

(Only showing to April 'cause I got tired of typing, but you get the idea)

I can modify the query if needed.

Hopefully I got my needs across.  :)

TIA
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
WHen you put the fields on the chart, you can set the "time frame".

Open the report
Right click the chart
CLick CHART EXPERT
Select the date field in the box
CLick CHANGE GROUP OPTIONS
Set the time to be QUARTER

mlmcc

Author

Commented:
mlmcc,

I'm not quite following...

What date field?  In which box?

There are no "group options" available that I can see.  In the Data Tab there is a frame named "Layout" which has several buttons, all disabled except for "Advanced" which is currently selected.  One of the disabled options is "Group" but I do not know how to enable it.

Commented:
I'm prettry sure this is not possible using your data structure and teh standard CR charts
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Author

Commented:
GJParker,

Fair enough.  So how do I need to change the data structure (or how do I use "non-standard" CR charts)?

Commented:
First step is to see if there is a chart available that does what you need, You can purchase add on charts here http://www.threedgraphics.com/tdg/products/tools/crchart/product_info.php

Only then will you knwo what format the data needs to be in

Author

Commented:
GJParker,
While the graphs from Three D Graphics look pretty cool, I'm not quite ready to plop down $1500 for one chart that may only be used one time.
You said that you used "For each record".  Normally for something like this, you would use "On change of", select a date field, click on the Order button below that and select "for each quarter".  Then you would get a bar for each quarter.  That's what mlmcc was suggesting in his first post.  But that's not an option with that data, since all of the quarters are in one record.

 If you can change the data, the simplest thing might be to put each month or quarter in a separate record.  You'd have to include a date, or the month or quarter (and the year), in the data.  With the date, the chart could group on that.  With the year and the month or quarter, the report could use a formula to create a date (or a quarter designation, like Q1, Q2, etc.), which could be used in the chart.

 James

Author

Commented:
I ended up using a different (line) chart.  But the recommendation to seperate the quarters in the records (one record for each quarter) made life just a little easier.

Thanks.
You're welcome.  Glad I could help.

 James

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial