Tree grid, dynamic Chart max and min

[RV2]

I'm using the trial version of the tree grid, and it's great. Really something XCelsius is missing and what our company is searching for.

But I got a little question: it looks like it isn't possible to give a range for the Chart Minimum and Chart Maximum so that every row of data has a different range in the graph.

To me it looks like only the first value are taken for each row of data.

[cmholden]

Will look into it and reply you as soon as I can. Thanks for writing.

[cmholden]

Right now it is not possible to set a range for each item in the tree. This feature is on the enhancement list ( a number of people have asked for this)
What you could do in the interim, is to convert the data in the column to indices so that each data item was based on 1 or 100, for example. This means that axis plotted for each graph would be the same.

[JasonG]

We just purchased the Invoista Tree Grid as well and would really like to see that feature.

Adjusting the values themselves might work for now and I will give it a shot, but it would be much nicer to have that feature mapped to row by row values.

We are attempting to set the range based on the level of hierarchy in the tree and it will be interesting to see which method (adjusting the data values or adjusting the range values) feels like it takes more effort to implement.

I will post back comments regarding our results.

---

One other thing regarding the max and min. I am attempting to set those values for two different bar chart columns in the same grid. This doesn't seem possible. One seems to keep inheriting the other's min and max (though I seem to have gotten around that with hard coded values).

To me, it seems very illogical for all of the columns to be restricted to the same min and max. Rows within the same chart I could see, but different columns (different charts) should have the ability for separate min and max mappings.

[JasonG]

Dynamically fitting data in a Group Tree Grid - Micro Bar Chart to a range of -100 to 100:

This is a quick write up of how I took values in a column and adjusted those (based on a group maximum value) to a scale of -100 to 100.

You could use this same logic and plug in any dynamic max value into the max value column. In my case I always set the scale to a symetrical positive / negative range.

This means that in my bar chart I set a hard-coded scale limit of -100 to 100.

The hard coded limits may be tweaked for a non-symmetrical scale. If all of the values are positive, set your scale from 0 to 100. Or flip that for all negative.

Where I display tool tips on mouse over of the bar, I map the data to my Column O.

Also, this code has not yet been completely tested. If you find glitches in the formula below, please reply with what those are.

Column O = Value to Chart

Code:

O2

Column N = Column Containing Group Max (this must be adjusted to the absolute value)

Code:

N2

N2 = MAX(ABS(MAX(O2)),ABS(MIN(O2)))
This is the top level, lower levels look more like:

Code:

=ROUND(MAX(ABS(MAX(O4:O8)),ABS(MIN(O4:O8))),1)

N2 could be easily derived in the report if the Xcelsius data is using LiveOffice data pulled through a report. In my case I actually generate a hierarchy level max value based on the level of hierarchy and fit the value to the max for the hierarchy. That way aggregate values do not skew the representation of lower levels. Making comparisons within levels of hierarchy more meaningful.

AdjMaxGrpValue = max group value (column N) shifted to be between 10 and 100

Code:

(N2/((10^ROUNDUP(LOG10(ABS(N2)),0))/IF(ABS(N2)<1,10,100)))

AdjBaseValue = base value (column O) shifted based on adjusting the max group value to a 10 to 100 value

Code:

(O2/((10^ROUNDUP(LOG10(ABS(N2)),0))/IF(ABS(N2)<1,10,100)))

AdjForNegative = Adjust the offset if base value is negative

Code:

IF(O2<0,-1,1)

ScaledOffset = How much of a minimum column value do you want to make sure you display. If you are using tooltips (which you probably do unless you have the literal value next to the bar chart), you will want this. To do this and spread that increase smoothly across all levels, I take a value of 10 (I tried 5, but did not like the level of effort it took to easily get the tooltip to pop up) and scale that according to the size of the value. If you are not using tooltips and do not want to include an offset, then don't add that.

OffsetSpread = (10 - ((AdjMaxGrpValue / 100) * 10))

Code:

(10 - (((N2/((10^ROUNDUP(LOG10(ABS(N2)),0))/IF(ABS(N2)<1,10,100))) / 100) * 10))

ScaleOffset = (AdjForNegative * OffsetSpread)

Code:

(IF(O2<0,-1,1) * (10 - (((N2/((10^ROUNDUP(LOG10(ABS(N2)),0))/IF(ABS(N2)<1,10,100))) / 100) * 10)))

ScaledValue = AdjBaseValue + ScaleOffset
ScaledValue = AdjBaseValue + (AdjForNegative * OffsetSpread)

Code:

(O2/((10^ROUNDUP(LOG10(ABS(N2)),0))/IF(ABS(N2)<1,10,100))) + (IF(O2<0,-1,1) * (10 - (((N2/((10^ROUNDUP(LOG10(ABS(N2)),0))/IF(ABS(N2)<1,10,100))) / 100) * 10)))

G2 final cell value is:

Code:

=(O2/((10^ROUNDUP(LOG10(ABS(N2)),0))/IF(ABS(N2)<1,10,100))) + (IF(O2<0,-1,1) * (10 - (((N2/((10^ROUNDUP(LOG10(ABS(N2)),0))/IF(ABS(N2)<1,10,100))) / 100) * 10)))

[cmholden]

This option is now available as of Feb 2/2010.
Download the new version and you can set the values in the Generic binding tab for each row of the grid.

There is an example xlf that comes with the download that shows you how to do this.

Mac

[JasonG]

Excellent. I will check that out.

After implementing that formula on a few columns I had planned to return and post an update stating that it is probably best to perform the calculation in the report if you are using Live Office data.

I think I will still do this as I have to imagine, though using dynamic ranges is easier, using a static range and adjusting the value to fit at the report level is probably better from an overall performance stand point when the chart is rendered in the grid.