Combination chart mouse-over values problem

[Former Member]

Hi!

I have a combination chart with 2 axis (primary - percents, secondary - numbers). My goal is to show correct mouse-over values (for series on secondry axis - numbers, on the primary - percents). Moreover I want to show percents with 2 decimal places.

In spreadsheet I have formated data (the real data for percents is 0.8206, 0.7638, etc.). So, I have percents (1st and 3rd columns) and general numbers (2nd) like this:

Indeed I have more data but this doesn't matter I think.

I found a strange way to show percents on percent data and numbers on general numbers (magic order of actions). My problem is that I can't figure out how to set 2 decimal places for mouse-over values. I got strange and incorrect results if I just set this property on the appearance tab:

The real data is around 99%, but tooltip shows 0.99% (if I set 1 decimal place in the appearance, it will show 9.9%). Really strange.

How to solve this???

I believe that it is possible because I did it once. Here is my other chart:

Now I can't figure out how I did it.

Suggestions?

[Former Member · Accepted solution]

It seems I know the way..

Prepare spreadsheet with proper format:

Add combination chart. Insert first series, name it “Numbers”, set its values (select a range). Insert second series, name it “Percents”, set its values (select a range), plot it on Secondary Axis.

Go to Appearance, Text, set Secondary Vertical (Value) Axis Labels Number Format to General, uncheck  Secondary Vertical (Value) Axis Labels.

Refresh “Numbers” series: go to General tab, select this series and reselect its values (Y) (just click the select range button and OK in the dialog).

Go to Appearance tab, set cursor to “Mouse-over Values”, change number format to Percent with 2 decimal places.

Check Secondary Vertical (Value) Axis Labels, set  Number Format to Percent with 2 decimal places.

Refresh “Numbers” again.

And again go to Appearance tab, set cursor to “Mouse-over Values”, change number format to Percent with 2 decimal places.

Refresh “Numbers” again.

Click Preview.

How to simplify this process?

[Former Member]

Hi,

Here is a trick you can use to fix the percentage issue in Charts/Scorecards etc.

In the spreadsheet format your data to Numbers, then in the Mouse over settings choose Number format to currency. Check the box next to Suffix and put % and set 1 or 2 in Decimal places based on your requirement and that s it.

Thanks

Ahtasham

[Former Member]

Hi Yury,

We have encountered similar issues in our combination charts as well.

What we learned is this:

-> the Regional settings in Windows for number formatting sometimes influence this "factor 100" issue.

What I notice in your original screenprint is that I see that a decimal comma <,> is used as decimal character.

In your second screenprint (with the "factor 100" issue) I notice that a  decimal point <.> is used as decimal character.

So maybe you changed your settings in Windows (from Europe setting to UK/US setting).

What we experienced is that this could lead to a misinterpreation by the xcelsius component. The decimals behind the comma character are now seen as full numbers.

What we do to prevent this is to define the number format of the MOuse-over values as General.

By doing so the number format is inherited from the cell reference in the Excel sheet.

So there you make sure you have defined the right number format:

Also we noticed that to get it right again (solve the decimals issue) the only way is to delete and recreate the xcelsius component.

Hope this helps you.

Cheers,

Marc

[Former Member]

I'm not sure I understand your problem, but here's what I got:

Here's what I did:

1. Add a Combination Chart

2. Setup the data as

A1 = 16

A2 = 19

B1 = 0.98

B2 = 0.99

3. Series 1 is A1:A2 on Primary axis

Series 2 is B1:B2 on Secondary Axis

4.Set bar chart transparency to zero

5. Set mouseover labels to 'Percentage' and decimals to '2'.

[Former Member]

Alex,

thank you.

1st problem is that the real data is around 99%, but tooltip shows 0.99% (if I set 1 decimal place in the appearance, it will show 9.9%). Really strange.

What mouse-over values are shown for your bar chart (when it is not transparent)? My goal is to show numbers, not percents. So, I want to show percents for your line and general numbers for your bar chart.

On my system I see:

[Former Member]

My bar chart would have shown the value 1900.00%

Mouseover values can have only 1 type: either they only show as percentages, or they only show as numbers.  But you cannot tell Xcelsius to show percentages for one series and numbers for another series, which is what you want to do.

The solution is to build two charts.  Create one bar chart, with mouseover value set to 'Numbers', and one line chart, with mouseover value set to 'Percentages'.  Remove the background, text, etc. from the Bar chart, then overlay it on top of the line chart. 

This will look exactly like a combination chart, but is actually two separate charts thus giving you the option of formatting each chart individually.