WARNING WARNING! DANGER NEW FM USER ALERT!

So I have taken just an Intro and Intermediate FM class, and quickly
get stuck on functions and how to set up appropriate tables.

Here is the type of data I have for selling golf clubs, all set up in
one table (UnitTable):

Type Club Qtr Yr UnitsSold
Iron Wedge 1 2006 80
Iron 5-iron 1 2006 20
Wood Driver 1 2006 60
....

I have this data for 12 quarters starting in Q1 of 2005.
I would like the user to do a Find based on Type and then the layout
would split back:

Iron: units sold table

Q1 Q2 Q3 Q4 Yr
2007 105 125 120 150 500
2006 100 118 110 130 458
....

Iron: quarter to quarter % change table

Q1 Q2 Q3 Q4
2007 (19.2%) 19.0% (4.0%) 25.0%
2006 18.0% (6.8%) 18.2%
....

Iron: year over year % change table
Q1 Q2 Q3 Q4
Yr
2007 5.0% 5.9% 9.1% 15.4% 9.2%
2006
....

and then the same for Woods

I have zero clue if I should be setting up functions within that one
table (to sum up appropriate Club for the Irons, to calculate the
Yearly sales, to calculate the quarterly and yearly % change, etc), or
if I should make different tables.

And beyond that, I don't actually understand how to do something
simple like sum up all the Clubs that you know are within a Type to
get that Type total for a certain quarter.
I am sure there is something out there on the net with a simple
example like this, but just couldn't find it.

I can do simple within-table functions like:
MonthlySalary = YearlySalary/12
But don't really understand the structure and functions needed for the
next level of complexity. Unfortunately I'm not taking another FM
class til next month

Any help would be great.
Thank you!

In article <1173740160.780887.100930 (AT) j27g2000cwj (DOT) googlegroups.com>,
"NewScanner" <nwaiterh (AT) gmail (DOT) com> wrote:


I am working on this one, but it's turning into a long reply. )

Basically you'll need a second table used to create these reports using
Relationship links back to UnitTable's data.


Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

On Mar 12, 4:56 pm, "NewScanner" <nwait... (AT) gmail (DOT) com> wrote:
Which version of Filemaker are you using to develop this solution? If
you're using the the more recent (8+) versions of Advanced/Developer,
you can make a nifty custom functions that will save you some
relationships or fields. Regardless, Assuming you're working in 7.0
or above and NOT with the developer version, I'll start you off with
the first problem.

Adding four fields. Q1, Q2, Q3, Q4 which are calc fields. Q1 =
Case(Quarter = Q1; UnitsSold;0), etc.

Then create 4 more fields, sumQ1, sumQ2, etc, which summary fields,
summing each of those calc fields.

Then you'll need to get your mind around Sub-summary parts of a
layout. You'll want a subsummary when sorted by year and put the 4
sumQ fields in that part along with the Year field. You won't need a
body part. Find all the Irons, sort by year (descending from your
example) and the sumQ fields should fill out as you hope for.

Once you get that straightened out, we can move onto the next problem.

G

Thanks for the start!
I'm using FileMaker Pro 8.5 Advanced (on a Mac, if that matters - I
don't think it does though?)

So I have added 4 calc fields in the UnitTable for Q1, Q2, Q3 and Q4.

Then for the sum fields, is that simply
sumQ1 = sum (Q1)
?

I have added those 4 calc fields to the UnitTable also (assuming that
was the right way)

I created a new Layout (in Standard Form, not sure if that was
correct)
Deleted Body
Added Part-> Sub-summary
chose 'Sub-summary when sorted by' then picked Year

I made my layout look like:
Region ______
Market ______
Type ____
Year ____
sumQ1 ____
sum Q2 ____
....

And it seems to work great! I think I'm ready for step 2!

Oh - I should note that the UnitTable also includes fields for:
Region (USA, Japan, etc)
Market (Pro, Amateur)

but hopefully that doesn't matter, I was trying to keep my question
really simple.

And it seems to work out so far!

I think you'd be better off with a separate Table, otherwise you're
going to be doing A LOT of messing around with Get Summary functions.

Firstly, FileMaker Pro is a database application designed for storing
records of data. It's not a data analysis application. Trying to do
data analysis in FileMaker is possible, but it can get very
complicated. It is often easier to use something like Excel to do data
analysis (and charts).

Having said that ...


PART 1 - UNITS SOLD
-------------------
Probably the easiest way within FileMaker to create such a report would
be to use a separate Table (you don't say which version of FileMaker,
in older versions this means a separate file). This new ReportTable
needs to have one record for each Year AND each Type, which has a field
that to combines the Year, the Quarter and the Type.
eg.
YearQ1Type Calculation, Text result
= Year & "-1-" & Type

YearQ2Type Calculation, Text result
= Year & "-2-" & Type

YearQ3Type Calculation, Text result
= Year & "-3-" & Type

YearQ4Type Calculation, Text result
= Year & "-4-" & Type

Where you can create records with ...

Record 1: Year = 2005 Type = "Wood"
Record 2: Year = 2005 Type = "Iron"
Record 3: Year = 2006 Type = "Wood"
Record 4: Year = 2006 Type = "Iron"
Record 5: Year = 2007 Type = "Wood"
Record 6: Year = 2007 Type = "Iron"
etc.


The original UnitTable also needs a similar field to combine the Year
and Quarter - this can not be a Calculation field, but must instead be
a normal Text field using an Auto-enter Calculation.
eg.
YearQuarterType Text, Auto-enter Calculation
= Year & "-" & Quarter & "-" & Type


Then you can create a set of Relationships to group togther each
Quarter's information from the main UnitTable.
eg.
Rel_Q1 From ReportTable to UnitTable
When YearQ1Type = UnitTable::YearQuarterType

Rel_Q2 From ReportTable to UnitTable
When YearQ2Type = UnitTable::YearQuarterType

Rel_Q3 From ReportTable to UnitTable
When YearQ3Type = UnitTable::YearQuarterType

Rel_Q4 From ReportTable to UnitTable
When YearQ4Type = UnitTable::YearQuarterType


Now you can create new fields in the ReportTable that total the related
UnitTable records for each quarter and for the year.
eg.
Q1_Total Calculation, Number Result, Unstored
= Sum(Rel_Q1::UnitsSold)

Q2_Total Calculation, Number Result, Unstored
= Sum(Rel_Q2::UnitsSold)

Q3_Total Calculation, Number Result, Unstored
= Sum(Rel_Q3::UnitsSold)

Q4_Total Calculation, Number Result, Unstored
= Sum(Rel_Q4::UnitsSold)

Yr_Total Calculation, Number Result, Unstored
= Q1_Total + Q2_Total + Q3_Total + Q4_Total


That's all the pieces in place that you need to create the FIRST of
your report tables above (the "Units Sold Table"). Simply Sort the
records by:

Type Ascending
Year Descending

and then Preview or print a Layout set-out something like:

[Type]: Units Sold Table
Q1 Q2 Q3 Q4 Yr

Sub-summary by Type (Leading)
-----------------

[Year] [Q1_Total] [Q2_Total] [Q3_Total] [Q4_Total] [Yr_Total]

Body
-----------------

Sub-summary by Type (Trailing, Page break after each occurrence)
-----------------




PART 2 - QUARTER TO QUARTER PERCENTAGE CHANGE
---------------------------------------------
Obviously you can get most of the "Quarter to Quarter % Change" report
figures by simple Calculation fields based on those already created
above, but the Q4 to following Q1 figure is a little more difficult to
obtain - for that you need to create another relationship in the
ReportTable itself to link to the previous yera's record. To do that
you'll need to create a new field with an Auto-enter Calculation:
eg.
PreviousYear Number, Auto-enter Calculation
= Year - 1

then you can create a Relationship.
eg.
Rel_PreviousYear From ReportTable to ReportTable
When PreviousYear = ReportTable::Year


Now you can create some more Calculation fields to work out the Quarter
to Quarter percentages.
eg.
Q1_QtQ Calculation, Number Result, Unstored
= If (Year = 2005,
"",
(Q1_Total - Rel_PreviousYear::Q4_Total)
/ Rel_PreviousYear::Q4_Total
)

Q2_QtQ Calculation, Number Result, Unstored
= (Q2_Total - Q1_Total) / Q1_Total

Q3_QtQ Calculation, Number Result, Unstored
= (Q3_Total - Q2_Total) / Q2_Total

Q4_QtQ Calculation, Number Result, Unstored
= (Q4_Total - Q3_Total) / Q3_Total

In the first Calculation you need to check that the current Year is not
the first one recorded (ie. 2005) otherwise you'll get a strange result
because their is no previous year's record.

Now it's time for another Layout, this time something like:

[Type]: Quarter to Quarter % Change Table
Q1 Q2 Q3 Q4

Sub-summary by Type (Leading)
-----------------

[Year] [Q1_QtQ] [Q2_QtQ] [Q3_QtQ] [Q4_QtQ]

Body
-----------------

Sub-summary by Type (Trailing, Page break after each occurrence)
-----------------


Format all the "QtQ" fields as Percentages to whatever decimal places
you want and using parenthesis for negatives.

Before Previewing / printing the Layout remember to Sort the records by:

Type Ascending
Year Descending



PART 3 - YEAR OVER YEAR PERCENTAGE CHANGE
-----------------------------------------
Now that the Previous Year relationship is there, this one becomes a
simple case of creating some new Calculation fields, again making sure
to ignore the first recorded year of 2005,
eg.
Q1_YoY Calculation, Number Result, Unstored
= If (Year = 2005,
"",
(Q1_Total - Rel_PreviousYear::Q1_Total)
/ Rel_PreviousYear::Q1_Total
)

Q2_YoY Calculation, Number Result, Unstored
= If (Year = 2005,
"",
(Q2_Total - Rel_PreviousYear::Q2_Total)
/ Rel_PreviousYear::Q2_Total
)

Q3_YoY Calculation, Number Result, Unstored
= If (Year = 2005,
"",
(Q3_Total - Rel_PreviousYear::Q3_Total)
/ Rel_PreviousYear::Q3_Total
)

Q4_YoY Calculation, Number Result, Unstored
= If (Year = 2005,
"",
(Q4_Total - Rel_PreviousYear::Q4_Total)
/ Rel_PreviousYear::Q4_Total
)

and put these on yet another Layout something like:

[Type]: Year Over Year % Change Table
Q1 Q2 Q3 Q4

Sub-summary by Type (Leading)
-----------------

[Year] [Q1_YoY] [Q2_YoY] [Q3_YoY] [Q4_YoY]

Body
-----------------

Sub-summary by Type (Trailing, Page break after each occurrence)
-----------------


Again, format all the "YoY" fields as Percentages to whatever decimal
places you want and using parenthesis for negatives, and remember to
Sort the records by:

Type Ascending
Year Descending




ALL DONE!!!!!
-------------
Since there is only one record per Year (for each Type), if you really
want to, you could add a pile of Summary fields and then create one
report Layout that gives you all three report tables on one page for
each Type ... but I'll leave that as your "homework" exercise. ;o)


I told you it was long. )


Note: Because of rounding issues, manually totalling of the Percentage
fields may not actually give a result of 100.



Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

I should have also asked -
should I be creating quarterly calc fields by *year* also?

Like do I need to have
Q1FY07 = Case(Year=2007, Quarter=Q1;UnitsSold;0)
....
(or whatever the exact formula is)

would I need to do that since I will want to display multiple years at
the same time?
Thank you-

Harry-
I'm starting to go through your post...
silly question-
when you wrote
Rel_Q1 From ReportTable to UnitTable
When YearQ1Type = UnitTable::YearQuarterType

Is the "When" a function?
I don't believe I have made relationships like that before-
Thanks

In article <1174427960.579638.173420 (AT) y66g2000hsf (DOT) googlegroups.com>,
"NewScanner" <nwaiterh (AT) gmail (DOT) com> wrote:

"When" isn't a FileMaker function. It's simply me telling you how the
Relationship decides which records to link to. )

Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

Sorry..a little confused by that part -

when you wrote:
Rel_Q1 From ReportTable to UnitTable
When YearQ1Type = UnitTable::YearQuarterType
....
Rel_Q4 From ReportTable to UnitTable
When YearQ4Type = UnitTable::YearQuarterType

Am I understanding correct - I should add 4 "links" (lines) on my
relationship screen:
one from ReportTable::YearQ1Type to UnitTable:YearQuarterType (and you
called this line Rel_Q1 I think
one from ReportTable::YearQ2Type to UnitTable:YearQuarterType (called
Rel_Q2)
one from ReportTable::YearQ3Type to UnitTable:YearQuarterType (called
Rel_Q3)
one from ReportTable::YearQ4Type to UnitTable:YearQuarterType (called
Rel_Q4)

So there are 4 links all going to the same YearQuarterType field?
Thanks-

In article <1174690750.558607.203110 (AT) e1g2000hsg (DOT) googlegroups.com>,
"NewScanner" <nwaiterh (AT) gmail (DOT) com> wrote:

Yes, correct. There are four Relationships - one for each quarter since
that's what groupings you're trying to display in each separate line of
the report.

They all link to the same UnitTable field (YearQuarterType) because
that field uses an auto-enter calculation to give it the appropriate
year, quarter, and type for each record. That means the relationship
can group together UnitTable rcords for each individual report line,
and each quarter and each Type.

Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)