Chart Data

[Former Member]

Hi All

I have the following requirement.

I have data in the following format in Excel which I imported into BO as it is.

This is Sheet 1 in BO

Company Name	Jan 2015	Feb 2015	Mar 2015	Apr 2015
C1	10	20	30	40
C2	50	58	39	27
C3	85	74	89	87

This is Sheet 2 in BO

Company Name	Jan 2014	Feb 2014	Mar 2014	Apr 2014
C1	52	25	39	68
C2	47	58	89	74
C3	12	87	96	41

I need a bar and line combined chart that displays the above data in such a way that when I filter for any one company name I should be able to see the column chart for last year's data (Sheet 2) and this year's data (Sheet 1) in line chart in the same chart combined together split over each month. Any help would be appreciated.

[former_member189638 · Accepted solution]

In the report, I believe you will have the dimensions CompanyName1, MonthYear1 , Measure1 and CompanyName2, MonthYear2 , Measure2. You need to merge the dimension CompanyName1 and CompanyName 2 .. Similarly merge MonthYear1 and MonthYear2.

Create a Chart with the option Combined Column Line Chart. In the Category Axis select the MonthYear merged Dimension. Add one more value Axis. Select Measure1 and Measure 2 on the Value Axis 1.

Create a Dropdown for Company name merged dimension in the Filter bar where you can select the

[Former Member · Accepted solution]

Unfortunately the data is coming from Excel so I don't have monthyear1 as a dimension

I have Company Name as dimension and Jan 2014, Jan 2015 and so on are all measure values.

Like Jan 2014 contains the values for Jan 2014 for all companies.

[Former Member · Accepted solution]

Hi,

It looks to me that the tables in your  Excel sheets are, in fact, crosstables, with company name and the months as the axis of the table and the measure as the body.

What I suggest you is to re-arrange the sheets so you come up with something like :

company name	monthyear	measure
C1	jan/14	10
C1	fev/14	20
C1	mar/14	30
C1	apr/14	40

This means, transpose the table and then follow Rakesh´s suggestion.

Regards,

Rogerio

[Former Member · Accepted solution]

I did try transposing the table in Excel but that did not work either. After transposing the company names became a row and the months became columns.

Just to clarify, if I have multiple company names according to the table you proposed, would I be listing them one after another like C1 and then C2 and so on in a single column.

[Former Member · Accepted solution]

Hi,

Yes that´s the idea

compa	monthyear	measure
C1	jan/14	10
C1	fev/14	20
C1	mar/14	30
C1	apr/14	40
C2	jan/14	50
C2	fev/14	58
C2	mar/14	39
C2	apr/14	27
C3	jan/14	85
C3	fev/14	74
C3	mar/14	89
C3	apr/14	87

Regards,

Rogerio

[Former Member · Accepted solution]

Thanks Rogerio

That worked perfectly