Bar Chart changing scale when Copy/Pasted

[Harry]

Happy New Year!

 

I've got a Bar Chart, months along the bottom, money on the Y. It works well, it's built on automatic date building and summarising for a KPI dashboard type display in an office.

 

I want to add another summary field, the first one is a 'Projected Sales' figure. The second one is an 'Actual Sales' figure, a third one will be 'Sent Sales'.

 

Because i want to display the three bars on top of each other, rather than by the side, I was planning to build the first one and then cut/paste two on top, changing the colour and field for the Y. That way, the 'Projected' figure is in red, as Sales come in and are entered onto the computer system, they build up and the second field Bar grows in Orange over the top of the red bar. Then, as products are being shipped out, this links to the SOP_ITEMS table and builds the monetary total of what has actually been shipped. All the numbers are working from what I can see. Does that explain what I'm trying to achieve?

 

My problem is that the second copied chart changes the size of the x dimension, so that when placed over the top and centered in edit mode, the Bar Chart on top is bigger than the one underneath. The labels are further in, the bars are different sizes.

 

What am i doing wrong? I can't figure it out at all.... Is it something to do with the way i'm summarising the numbers from different sources? Or something simpler?

 

Thanks and I hope you are all feeling good for the new year!

 

 

[Harry]

OK - this is still cracking me!!

 

I have a bar chart - Months along the X and then a Calc field which is a SUM of another TO etc etc.

 

It works. 

 

I set the Y to have 0-7000 increments and when rendered, the scale reads 0 - 50 - 100 - 150 - 200 - 250 - 300 - etc etc.

 

I then copy and paste that graph and change the Y to be another Calc field, another SUM of a different field within that same TO from above. The scale then reads 0 - 40 - 80 - 120 - 160 - 200 etc etc.

 

It seems the values are creating the scale, is that right?

[Harry]

OK, so I have sorted the 'Y' axis interval out - now i have this issue with the 'X' axis, but only when changing the 'Y' axis data!!

 

Here's the chart Copy/Pasted onto itself.

 

 

And here's the same chart again but this time i've just clicked into it and changed the Data Field on the Y Axis for a different field.

 

[Harry]

I have used an 'IF' statement to set the Calc field to '0' if there is no SUM data, but that doesn't work.

I've tried setting it to '50,000' if there's no SUM data, but that doesn't work, either.

I thought perhaps that there was no data in the field, it would resize the scale.

[Harry]

Nobody up for fixing this?

 

I will pay a developer for the correct fix or answer to enable me to do this.

 

Thank you!

[Aussie John]

This is certainly a problem with the native charts.

[Harry]

An actual bug, you think?

The only reason i've really persisted with it is because FM 'Phone Support said they couldn't answer it (doesn't fit within their parameters) but I should ask online because there is a solution....

 

If this is just 'broken' then I'll drop it and try and find another charting solution.

 

Harry

[LaRetta]

I suggest that you ask on line here at FM's Report An Issue:  http://forums.filemaker.com/hives/1eea103f05/summary

 

It is great to post here and many times we can help folks realize a behavior is or isn't a bug but if unsure or if we suspect a bug, post there where FMI usually responds.  :-)

[Aussie John]

Feature or bug I don't know but charts self size depend and on content and size of the various texts.

[comment]

 i want to display the three bars on top of each other, rather than by the side, I was planning to build the first one and then cut/paste two on top,

 

Is there a good reason for this complication? Stacking two bar charts on top of each other presumes that the front chart's values are consistently smaller that those in the background. If that's really the case in your scenario, why don't you simply use the stacked bar chart type?

 

 

FWIW, I wasn't able to reproduce your problem, albeit using version 11. Provided that the two charts have the same Y-Axis range limits and the same layout dimensions, they align perfectly.

[Harry]

Yes, the requirement is because both data sets start at '0' and there is a requirement for the viewer to see when one value (behind) has been reached or surpassed. A stacked barchart suggests to the viewer that each section is a part of a total, displayed on the Y. So the first part is 0-50, the second part stacked on top represents data from 51-100 etc etc etc.

 

All charts stacked on top of each other represent a value of the same target.

 

Not sure if that's explained well enough....

 

I can see that chart resize itself, but it's odd that it seem so to be changing the actual size of the representations visible based on the content of the data. Odd. I would have thought the chart would display and the values would represent based on the data. To skew them on that seems like the antithesis of a good chart.... ahwell.

 

Thanks for the link  LaRetta, I will post and see what they say!

[comment]

there is a requirement for the viewer to see when one value (behind) has been reached or surpassed.

 

I believe that the standard chart type for this type of comparison is multi-series column. I don't know why you don't want to use it.

 

Alternatively, you could use an area chart with semi-transparency:

 

 

 

A stacked barchart suggests to the viewer that each section is a part of a total, displayed on the Y.

 

I guess I am still missing something here. How is your stacking two charts on top of each other (assuming you do succeed in doing that) going to be visually different from a stacked bar chart? In both cases, you won't be able to see the 'behind' value when it has been reached or surpassed.

[Harry]

It would be different from a stacked bar chart because if the two values were '50', for example, the stack would read '100' on the Y scale, leaving the viewer to examine the chart to see how much value each section of the stack holds.

 

Whereas, I'm not really trying to show the value, but rather a 'summary' in a view of the question 'has the target been reached?'. So as the 'Entered_Sales_Orders' sum total rises, it gradually approaches the 'Target_Sales_Order' figure behind it. When that is achieved, the viewer cannot see the target behind, which is no longer relevant. Then when 'Shipped_Sales_Order' sum total starts to rise, that reaches becomes the next target for the team to chase. Not getting the orders in, but getting them out. So the chart shows these things happening, rather than definitive numbers, which are seen in other reports.

 

I suppose I could use an area chart. But I don't believe it's technically correct to use this sort of chart in this instance. The values of Y are in the month or the next month, but cannot be inbetween. So, showing data inbetween months feels wrong to me. 

 

However, for the sake of answering the original questions of the viewer, it could certainly be the better choice. I will adapt a layout and try it out.

 

Still, it's really started to bug me now!

[comment]

It would be different from a stacked bar chart because if the two values were '50', for example, the stack would read '100' on the Y scale,

 

No, it wouldn't. Not if you wanted to show Actual vs. Projected. I am afraid you misunderstood me on this point. You would show Actual as the bottom part of the stack, and a calculation of Projected - Actual, or more correctly Max ( 0 ; Projected - Actual ) as the top part. That means the stack would never be taller than the highest of the two values, and the difference between Actual and Projected could be seen only when Actual is less than Projected - exactly as you describe.

[Harry]

Ah yes, I did misunderstand, sorry. Yes, I could run the 'opposite' maths to build that column and it would work well, but it wouldn't show when the entered sales are greater than the forecast sales, and the shipping sales. These things can overrun the original target and be 101%/200% greater than the original target.

 

Now you've explained it further, I remember running that chart initially; and that reason is why I went with layered Column charts.

 

I think you're right though, I'm trying really hard to get something with relatively little value when something that would be very easy could deliver nearly as much value.

 

Diminishing returns, but I've never come across a problem that I couldnt' fix or get right in FM. Taken months, sure, but never to the point that it was not solveable.

 

Anyway, I will render the chart as an area line with transparency and see how the viewers feel about that. It's for them, after all.

 

Thanks for the attention to this!

 

H

[Harry]

Ah yes, and I lose control of the colour of the chart, too.

 

Our standards are Red/Orange/Green, basic traffic lighting. CAD machines have it, Door opening warning signs have it, target reports have it. So, to have a chart that is communicating these same things, but in a 'Muted Rainbow' colour scheme, it jars with the rest of the output and turns people off. It stops becoming a very quick viewable summary and needs to be studied to understood - the antithesis of good charting!

 

Why I can't select and colour each data series, I don't know. But i need contrasting colours, not complementary that look pretty on paper. That's not good data representation.

[Harry]

I suppose I could resolve the first part of the above issue by having an 'IF Forecast < Entered, THEN do Maths one way, ELSE do it the other' and it would draw a different total so the chart column height would rise.

Thank you Comment!