negative values in stacked bar

[Former Member]

Hi

I am using a custom chart. In one column (stacked bar) I have two values (2 and -1). I would expect the chart to draw one bar up from 0 to 2, and another bar down from 0 to -1 (as Excel would do in this situation).

However what actually happens is it draws the first bar from 0 to -1 then draws the second bar over this from -1 to 1.

As usual, any help is much appreciated, and if you need any further info please just ask.

Nick

PS Here is my data

  <?xml version="1.0" encoding="UTF-8" ?> 
- <Rowsets DateCreated="2007-10-25T15:47:11" EndDate="2007-10-25T15:47:11" StartDate="2007-10-25T14:47:11" Version="11.5.3">
- <Rowset>
- <Columns>
  <Column Description="" MaxRange="1" MinRange="0" Name="DateTime" SQLDataType="93" SourceColumn="DateTime" /> 
  <Column Description="" MaxRange="1" MinRange="0" Name="title" SQLDataType="1" SourceColumn="title" /> 
  <Column Description="" MaxRange="1" MinRange="0" Name="Category" SQLDataType="1" SourceColumn="Category" /> 
  <Column Description="" MaxRange="1" MinRange="0" Name="Qty" SQLDataType="8" SourceColumn="Qty" /> 
  </Columns>
- <Row>
  <DateTime>2007-10-20T00:00:00</DateTime> 
  <title>Prior</title> 
  <Category>DepReq</Category> 
  <Qty>-1</Qty> 
  </Row>
- <Row>
  <DateTime>2008-03-15T00:00:00</DateTime> 
  <title>Post</title> 
  <Category>DepReq</Category> 
  <Qty>-1</Qty> 
  </Row>
- <Row>
  <DateTime>2007-10-20T00:00:00</DateTime> 
  <title>Prior</title> 
  <Category>PurRqs</Category> 
  <Qty>0</Qty> 
  </Row>
- <Row>
  <DateTime>2008-03-15T00:00:00</DateTime> 
  <title>Post</title> 
  <Category>PurRqs</Category> 
  <Qty>2</Qty> 
  </Row>
- <Row>
  <DateTime>2007-10-20T00:00:00</DateTime> 
  <title>Prior</title> 
  <Category>variance</Category> 
  <Qty>-1</Qty> 
  </Row>
- <Row>
  <DateTime>2008-03-15T00:00:00</DateTime> 
  <title>Post</title> 
  <Category>variance</Category> 
  <Qty>0</Qty> 
  </Row>
  </Rowset>
  </Rowsets>

Here is my Display Template

<?xml version="1.0" encoding="UTF-8"?>
<iChart AxisLabelColumns="title" ChartType="Custom" Decimals.1="0" 
Decimals.2="0" Decimals.3="0" GlobalDecimals="0" GlobalMaxRange="30.0" 
GlobalMinRange="-10.0" GlobalServerScaling="false" 
HorizontalGridColor="#E0E0E0" InterpolateLines="false" 
LabelColumns="Category" MaxRange.1="60.0" MaxRange.3="60.0" 
MinRange.1="-20.0" MinRange.3="-20.0" PenType.1="StackedBar" 
PenType.2="StackedBar" PenType.3="Line" SaveDate="10/25/2007 15:27:33" 
ShowHorizontalGrid="false" ShowTagDescription="false" 
ShowTimeControl="false" ShowVerticalGrid="false" ThreeD="true" 
UseGlobalDecimals="true" UseGlobalRanges="true" ValueColumns="Qty" 
Version="11.5.3" XAxisLabelDepth="2" XAxisShowLabels="false" 
XAxisThreeD="false" XAxisTicksPerLabel="1" YAxisThreeD="false" 
ZeroBasedCenterline="true" ZeroBasedScale="true"/>

[Former Member]

Hi

I'm bumping this up in the hope that someone can tell me if I'm doing something wrong, or if this is a bug in xMII. If it is a bug, is it fixed in v12?

Please let me know if you need any further details.

The workaround I'm using is to split the data into double colums, with all the +ve values in the first, and all the -ve values in the second, with the variance line having the same value in each. Doesn't seem very efficient, and definitely makes the graph harder to read.

[Former Member]

Be sure that you have "Zero Based Centerline" set to true/checked.

[Former Member]

Oops. Noticed it was a "stacked bar". Negative values aren't permitted in a stacked bar. You should use a waterfall chart. That is precisely the purpose of a waterfall chart - to show both negative and positive contribution towards a total.

[Former Member]

Thanks, but I'm still confused and frustrated!

If "Negative values aren't permitted in a stacked bar" (no mention of this in the help)then why does it work fine with a stacked bar? Just not with lines of Stacked Bar type in a custom chart.

I'll look further into the waterfall chart, though I don't think it's what I want.

[Former Member]

Actually, looking at your data, I think it (waterfall chart) might be exactly what you want. It's a very cool way to visualize that type of information.

[Former Member]

Perhaps that's my fault for over-simplifying my example.

As well as the "Prior" and "Post" periods I also have 22 weeks in between, and could have up to 10 "Categories". Unfortunately the Waterfall chart doesn't seem to differentiate between these Categories (only showing one colour for +ve and another for -ve). It also doesn't stack the bars in each period, so instead of the two bars I'm stuck with just now (222 in total), I could have up to 10 bars for each period (1022). At the moment my data is padded with zeroes (so that catA weekX is in the same stack as catB weekX), so my waterfall chart is covered with zeroes running together, and I couldn't figure out how to switch these labels off.

So basically the Waterfall chart doesn't seem to be the way forward for me, though I can see how useful it would be for a smaller dataset.

Hope all this made sense.

[jcgood25]

Nick,

I would just use the StackedBar mode, since it will do a better job of determining the pseudo pen names you want based upon the data mapping of Category / Title. If there was a particular reason for using Custom mode then please advise.

This DT seems to work just fine with the sample dataset you provided earlier:

<?xml version="1.0" encoding="UTF-8"?><iChart AxisLabelColumns="title" ChartType="StackedBar" GlobalMaxRange="1.0" GlobalMinRange="-1.0" GlobalServerScaling="false" HorizontalGridColor="#E0E0E0" InterpolateLines="false" LabelColumns="Category" SaveDate="11/06/2007 15:05:57" ShowHorizontalGrid="false" ShowMouseTracking="true" ShowTagDescription="false" ShowTimeControl="false" ShowVerticalGrid="false" UseGlobalDecimals="true" UseGlobalRanges="true" ValueColumns="Qty" Version="11.5.3" XAxisShowLabels="false" XAxisThreeD="false" XAxisTicksPerLabel="1" YAxisThreeD="false" YAxisTicksPerLabel="1" ZeroBasedCenterline="true"/>

I would also recommend using "NA" instead of "0" to keep your dataset squared up, unless the values are truly zero. If you are building the dataset in BLS then use the nullnumber expression in the link editor instead of a hardcoded 0. If the dataset comes from a query, the null values should be sufficient.

Regards,

Jeremy

[Former Member]

I'm using custom mode so I can display a variance line also.

I tried the nullnumber, but it draws bars from the bottom of the chart up to 0.

[Former Member]

Do you have Global Auto Scale checked for your Pen Details. If not then try that,this should fix your problem.

[Former Member]

No, this doesn't make any difference. I tried lots of combinations of range settings, but nothing seemed to work.

If I change it to a "StackedBar" the bars for "DepReq" and "PurRqs" are shown correctly, but of course it tries to stack my variance on top which is no use.