Some data series missing from stacked bar chart

[RogerC]

I have a table similar to this:
Across row 1 are header labels (A1 = 'CITY', A2 through AJ1 = Week1, Week2, Week3, etc)
Down column A are the city names (A2 through A11 = Phoenix, LosAngeles, Dallas, etc)
The data in B2 through A11 is the weekly total sales for each city.

I have checked the Source Data settings for the chart and each of it's city series. The chart appears to be pointing to all of the correct data.

Here is the problem:
For week13 (the most recent week) only the data in Rows 4, 5, and 8 are being plotted in my stacked bar chart. The data in rows 3, 6, 7, 9, 10 and 11 is missing from the chart. Even though the Source Data settings show them to be included. (These cells have actual data, not blank or 0).

The data series that are missing varies from week to week (rows 5, 7, 10 are missing from week11 chart data).

I have stared at this until I'm blue in the face, but cannot determine what is wrong. Can anyone suggest something I might look for?

 

[RogerC]

IluvPivots,

Thank you so much for your offer to review my spreadsheet.

Unfortunately, I needed to present the charts the next day so I spent the evening rebuilding them and re-establishing links. For some reason when I recreated the charts (using the very same data and series ranges) they plotted all of the data as expected.

I will know to look for the formatting issue in the future.

Thanks again for your help... you are truely a dear!

 

[RogerC]

If the data comes from SAP to BO to Excel as text, it will be plotted as zero values, no matter what it looks like.

Jon Peltier,

Thank you for explaining the underlying problem and how to avoid it. My company is currently implementing SAP worldwide (100,000 employees) and I can image a great deal of pain over this issue down the road.

Given your explaination of the cause, does it make sense that simply recreating the charts fixed the missing series?

Thank you.

 

[Jon Peltier]

If the data is present but not recognized as numeric, you can use a simple VBA procedure to convert it. Select a range and run this:

Sub MakeDataNumeric()
  Dim rArea As Range
  If TypeName(Selection) = "Range" Then
    For Each rArea In Selection.Areas
      With rArea
        .NumberFormat = "General"
        .Value = .Value
      End With
    Next
  End If
End Sub

In fact, you might do this on the dumped data as soon as you get your hands on it from the SAP source.

 

[IluvPivots]

RogerC-
I'm glad I could provide input.

I get so much help from this board in the few times that I have posted. I hope that I can return to favor to someone else.
Best of Luck!

J