Creating Panel Charts / Enhancing Bar Charts to resemble Panel

[lucasdavis500]

I would like to create sort of a 'panel' chart with my data for the end user to view.

Currently, My data looks like this:

The chart on the left is the "TOTAL" view, and the middle, right charts are "BREAKDOWNS" by different PRODUCT TYPE

I want to scale these to the TOTAL chart, which I did with a static step/min/max, which eventually I will probably create a formula for in order to keep the scale the same as the left (haven't thought of how to do that yet)

but I also want the AXIS of the middle/right chart to go away, because the user can see from the left what the values are, HOWEVER, when removing the axis, the GRIDLINES are removed, so it becomes difficult to tell what the trend is, etc.

Has anyone else done something similar to this, so that these charts aren't as hard to read.

Looking for suggestions.

[johnw]

No. The inline load would be exactly what you see there. Assuming that 'Financial Card' and 'Vehicle/Home EQ' are the two products you want to see, and that field is [Product]. I've seen hints elsewhere in your posts that it might be [PRODUCT TYPE] instead. Use whatever your actual field name is.

Think about what this new table, this new field does. There are three values for it - 'Total', 'Financial Card', and 'Vehicle/Home EQ'. 'Total' maps to the two other products, so it will be a total for both products. 'Financial Card' and 'Vehicle/Home EQ' map only to themselves, so they will just show themselves. So the trellis chart will have three sub charts - one for the total of Financial Card and Vehicle/Home EQ, one for Financial Card, and one for Vehicle/Home EQ.

[lucasdavis500]

I realize what you are saying now, I was trying to wrap my head around it, but when I did it, it worked.

Unfortunately, I'm not sure if the trellis chart is going to be the proper way to go. The graphs are too small relative to the size of the chart area, and when I use CTRL + SHIFT there isn't a box to resize the chart area... only the legend and title. The graph's aren't very pretty....

[johnw]

That's strange. I get the red box to resize when I control shift. Maybe remove the label for Month, months vertical or diagonal, shrink the font for the titles, no caption, sort in load order so Total comes first, maybe get rid of the legend and handle it in some other way. I've got trellis charts that look good and use up all of the space given to them.

But I don't know of any way to show the axis in only the first chart, so they'll always waste space in that regard. In the trellis charts I'm looking at, I actually removed the axis, and instead put values on data points, but only for certain data points (if([Type]='Actual',black(),argb(0,0,0,0))). It was enough to give a sense of scale and some exact data without the wasted space of repeated axes. Anyway, a trellis chart is probably not ideal visually, just in some senses the simplest way to do basically what you were after.

But we can certainly go back to tackling it as three charts if you prefer. It does give you a bit more control over presentation. We can synchronize the axes even when its three separate charts.

[lucasdavis500]

I think that's what I'm going to try to do, but I need to pull it off visually. It doesn't look bad, just doesn't look great, and execs (always) want great.

I used:

max(aggr(rangemax(SUM({<[Not_e-Oscar_Dummy] = {'0'}, FCRA_dummy = {'1'}, Month -= {$(vCurrentMonth)}, CurYTDFlag = {'1'}>} CET_Dummy),SUM({<ACDVDummy = {'1'}, Month -= {$(vCurrentMonth)}, CurYTDFlag = {'1'}>}ACDVDummy)),Month))

as you suggested, for my static max, where the formulae are from the "Total" chart, on the left,  which made the scales a little better, I just wish QV allowed you to remove the axis, but keep the gridlines where the axis would have been. Also, using that formula for the static max in the far most left graph makes the "max" less than the actual maximum values for the expressions, I'm not so sure that's visually appealing, but then again I'm not the best at making visualizations either...

[johnw]

So you're saying that these are the two expressions in your total chart?

SUM({<[Not_e-Oscar_Dummy] = {'0'}, FCRA_dummy = {'1'}, Month -= {$(vCurrentMonth)}, CurYTDFlag = {'1'}>} CET_Dummy)

SUM({<ACDVDummy = {'1'}, Month -= {$(vCurrentMonth)}, CurYTDFlag = {'1'}>} ACDVDummy))

And the chart dimension is Month? But this:

max(aggr(rangemax(SUM({<[Not_e-Oscar_Dummy] = {'0'}, FCRA_dummy = {'1'}, Month -= {$(vCurrentMonth)}, CurYTDFlag = {'1'}>} CET_Dummy),SUM({<ACDVDummy = {'1'}, Month -= {$(vCurrentMonth)}, CurYTDFlag = {'1'}>}ACDVDummy)),Month))

Isn't putting the highest bar of your total chart at the top? So a bar or two in that picture you show are being cut off at the top?

One way to hide the axis is to change the axis font color to 100% transparent. It still takes room, but doesn't show. Another way is to remove your chart borders (mine "never" have borders - waste of "ink"), and layer the charts, the 2nd chart behind the first, 3rd chart behind the second, with the numbers hidden in that way.

[lucasdavis500]

No, it shows the full value, it's just that the max value is basically pushed to the caption of the chart when using the AGGR function as my static max. It's not giving the bars room to breathe, in my opinion I don't think it looks as good as it did when it let it QV choose the axis ( it choose ~2500 for max), but then I would have to resize the other two axis if I did that. I think I'm confused as to what you mean by layer, do you mean so the gridlines still show?

[lucasdavis500]

I've removed the borders, made the axis transparent, and created a text object to hold all three charts to appear as a "panel", my only issue now is that the gridlines don't match up, even when using the same formula for static max, on my third chart. I believe this is an issue because my data only goes from May - Today whereas the other two are Jan - Today. Over time I believe these will align

[lucasdavis500]

Changing the axis font color was actually a great idea - I'm not sure why I hadn't thought of that. Once you change the color you can remove the axis space by dragging the chart area left. It pulls the axis "off" the chart, but we don't care about that anyway

[johnw]

If you want room for your bars to breath, you can increase your maximum slightly. Like use 1.05 * the current expression. Fiddle with the number until you're happy. Maybe round up to the nearest 100 or whatever number makes sense.

For the gridlines not lining up, I'd arrange your month labels vertically or diagonally rather than horizontally, and I'd use 'Sep' instead of 'Sept', but probably the best thing to do is the control click thing to get the red box and make sure the red boxes line up so that any content in them lines up.

[johnw]

For layering, I meant have the charts overlap, and use the layer option on the layout tab to say which one is on top, middle, and bottom. But it seems like you've got it looking like you want without doing that, so don't do the layering thing.