5 Replies Latest reply on Dec 10, 2014 7:48 AM by philmodjunk

# Summing and grouping values based on multiple years

### Title

Summing and grouping values based on multiple years

### Post

Hi there

I'm a total noob with regards to FM but managing to get through some of it.  One thing I need to be able to do is create a sum of values by Customer, Year and then by Type.  From there, I need to able to use these values with each other for further maths.  For example, this is laid out on the table similar to this and I need to do (1+2)=Sales (3)=Expenses, (Sales-Expenses = Profit).

CustomerID|Year|Value Type|Value

1                |2011| 1              |1,000

1                |2011| 2              |500

1                |2011| 3              |50

1                |2012| 1              |1,100

1                |2012| 2              |500

1                |2012| 3              |40

2                |2011| 1              |5,000

2                |2011| 3              |1,000

etc for many customer ID',s but all with the same basic format.

Probably simple for the initiated!

Thanks

• ###### 1. Re: Summing and grouping values based on multiple years

To start, take a look at this tutorial for summary reports: Creating Filemaker Pro summary reports--Tutorial

That may not get you up and running on every detail so feel free to use Post a new Answer to ask follow up questions.

• ###### 2. Re: Summing and grouping values based on multiple years

HiPhilModJunk

Thanks for the help on this, it's given me what I was looking for and created another question at the same time.

I've summed and grouped my values by Type, Year and Customer and this generates on a layout.  I've removed the Body part as it doesn't lend itself to what I need to do.  This all looks pretty good:)

What I need to do now is to get a calculation in a trailing group, not sure following some tests I've done if this can be done.

I can get a summary there, I can get two summaries there, but I can't get "Summary Value Type A - Summary Value Type B", or furthermore, a percentage calc.

So I need to keep displaying what I have, people want to see that info, and then get further calculated info alongside it or beneath it.

I hope that makes sense,

• ###### 3. Re: Summing and grouping values based on multiple years

If you refer to a summary field in a calculation, you get the "grand total" value rather than the sub total that you see when you place it inside a sub summary layout part. To access the sub total for use in a calculation, use the GetSummary function--something that you can look up in FileMaker help. Make the "break field" parameter the same as your "when sorted by" field specified for the sub summary layout part. (Note: unlike sub summary parts, the break field has to be defined in the same table as the summary field.)

• ###### 4. Re: Summing and grouping values based on multiple years

PhilModJunk

Great explanation - thanks.  I had just started looking at that little nugget and what you've written really helped.

I'm now summarising and GETting various data groups where I need to which means I can do A/B*100 and other funky stuff.

Thanks!

• ###### 5. Re: Summing and grouping values based on multiple years

You can also do A/B and then format the field as a percent and not need to multiply by 100.