Someone help, this is driving me insane!

[liltbrockie]

Please for the love of god will someone have a look at this incredibly simple setup and make the graph work for me!!

I'm just trying to show a chart grouping sales by month but it won't work.. It just keeps grouping it by Year.

I'd be very grateful if someone could post back a corrected way of doing this!

sum.zip

[ddreese]

I think I got what you were looking for.

First thing I did was change the format that your dates appeared to just "Month Year", so "January 2009".

I then sorted the data by date, and then in table view on the first layout I created a "Trailing Group" on the date field (read the filemaker help file and follow along in the tutorial .fp7, I just learned this yesterday). That groups the data by those "Month Year" formatted date fields.

I then messed around with your chart layout removing the extra sub-summaries (while adding one based on the Date field), and then changed the x axis data on the chart to be your total field, not the summary. There may have been some other things in there, so you may have to tinker around to see all my changes.

I'm not sure if this is what you want but it seems to do what I thought you want.

Running_totals.zip

[liltbrockie]

Hey thanks for looking! Its not working quite right though. See attached pic... what i need it do so is summarise each month.. add them up. At the moment the graph is just using the last value of the month.

You see we have one record in the database for each sale we do ... so there will be 20 or so orders (records) a day.

http://www.kitchenappliancesforum.co.uk/temp.jpg

Edited March 11, 2010 by Guest

[bruceR]

The graph is doing what you told it to do. If you want it to use field "Summary" you need to set it up to use that field. But you haven't.

This may be in part a result of confusion caused by your field names. The field "Total" is NOT a total field. You have several records for each date. Maybe it should be titled "Sales". You have a field "Field". Should it be "Region" or "Sales person" or something else that distinguishes the same-day records?

[ddreese]

Oh yea, duh. I think it threw me because in the example data it has two for 1/1/2009 and I thought it worked right. It did not :

Maybe second time's a charm. I created a calc field that calculates every order date to be the same day for every month (you may have to change it to make it work with your date format). I sorted on that, created the trailing summary on that, and then changed the chart back to use the summary field for the y axis, I think? I dunno, look at the file, haha....

Disclaimer: There's probably a better way to do this.

Running_totals.zip

[liltbrockie]

I think you've cracked it.. well done, thank you.

[bruceR]

But you already had a field for that; your Link field. Just set up the sub summary to be based on Link, and sort by Link.