Okay guys...Here's a tricky one that I, nor my co-workers, cannot figure out! However, we need to pull this off due to a report for upper management.
I am comparing the WTI OK Cushing Spot Price for Crude Oil against hourly rates for two different service types from 5-7 vendors. The problem is, in order for you to visualize the true movement in the price of oil, it needs to be "charted" as a daily price. Therefore, my first set of data is: (1), daily spot price of WTI from January 01, 2007 to December 31, 2010, which is 1009 different numbers. My second set of data is an hourly rate from Vendors A through F by year. Therefore, an example of one of my sets of vendor data would be: (2), annual rate of Vendor A for each year, between 2007 - 2010, which is only 4 different numbers.
I've tried creating a line chart with a secondary axis to allow for the WTI Spot Price to reference the secondary axis and primary axis, while the Vendor Data references the other. I've tried combo charts with line/column clusters. I've tried everything, however since the Unit of Measurement (UOM) for the point in time is different (annual rate vs. daily price), I can't get the two to vibe on one chart.
Therefore, my only solution has been to overlay two different charts after formatting like crazy (which, honestly, looks pretty shoddy).
If anyone can tell me how to create a graph with varying units of measurement (annual rate vs. daily rate), I would be in forever debt to you.
Sincerely,
Lost Number Cruncher.
I am comparing the WTI OK Cushing Spot Price for Crude Oil against hourly rates for two different service types from 5-7 vendors. The problem is, in order for you to visualize the true movement in the price of oil, it needs to be "charted" as a daily price. Therefore, my first set of data is: (1), daily spot price of WTI from January 01, 2007 to December 31, 2010, which is 1009 different numbers. My second set of data is an hourly rate from Vendors A through F by year. Therefore, an example of one of my sets of vendor data would be: (2), annual rate of Vendor A for each year, between 2007 - 2010, which is only 4 different numbers.
I've tried creating a line chart with a secondary axis to allow for the WTI Spot Price to reference the secondary axis and primary axis, while the Vendor Data references the other. I've tried combo charts with line/column clusters. I've tried everything, however since the Unit of Measurement (UOM) for the point in time is different (annual rate vs. daily price), I can't get the two to vibe on one chart.
Therefore, my only solution has been to overlay two different charts after formatting like crazy (which, honestly, looks pretty shoddy).
If anyone can tell me how to create a graph with varying units of measurement (annual rate vs. daily rate), I would be in forever debt to you.
Sincerely,
Lost Number Cruncher.
