My bad. I should have tested it. Tried to do this now and saw that
unfortunately FM5/6 cannot summarize repetitions individually.
So the solution would be to create 13 fields to get qty for each type
Case( ProductType = 1, Qty )
Case( ProductType = 2, Qty )
...
Case( ProductType = 13, Qty )
and then 13 summary fields to summarize these 13 ‘columns’.
Sorry for misleading.
Kind regards,
--
Mikhail Edoshin
Information Analyst
Skeleton Key
[EMAIL PROTECTED]
On Jul 24, 2007, at 7:35 AM, Nicholas Geti wrote:
Hi Mikhail,
I got the first part of your solution working. But I don't see how
to get the "Total of Qty by Type" working. Starting with defining
it as 13 repetitions similarly to "Qty by Type" and then converting
it to a Summary field turns off the repetitions and converts it to
a single value object.
----- Original Message ----- From: "Mikhail Edoshin"
<[EMAIL PROTECTED]>
To: <[email protected]>
Sent: Sunday, July 22, 2007 11:12 AM
Subject: Re: Cross tabs possible?
Hi Nicholas,
I don't see a "GET()" function in my version of Filemaker which
is Pro 5.0
So you have FM5 :) It's possible in FM5 too, but may be somewhat
slow. Create a global number with 13 repetitions. Fill it with
numbers from 1 to 13. This will be a substitute for Get
( CalculationRepetitionNumber ). Let's name it Repetition Number.
Now define an unstored calculation Qty by Type with 13
repetitions like that:
Case( Extend( ProductCode ) = Repetition Number, Extend( Qty ) )
Now if you place the field on the layout and display all
repetitions horizontally, you'll see that it shows something like
that:
ProductCode Qty by Type
[1] [2] [3] [4] [5] ... [13]
1 5
3 2
1 4
13 ... 4
Here 5, 2, 4, and 4 are sample quantities. Now you define a
summary field = Total of Qty by Type and set it to summarize
repetitions individually. It automatically catches it needs to be
13 repetitions long as well. Place the field in the summary part
and it will give you summaries by column.
ProductCode Qty by Type
[1] [2] [3] [4] [5] ... [13]
1 5
3 2
1 4
13 ... 4
--------------------------------------
Total 9 2 ... 4
It should work correctly with all summary types and with all
subsummary parts, etc. Basically it's same as if you defined 13
individual fields like
Case( ProductType = 1, Qty )
and then 13 summary fields for them. This method uses less fields
and is more flexible. The disadvantage in FM5 is that it has to
use a global field and this makes the calculation unstored. If
this is a problem, resort to the 13+13 fields solution.
I didn't actually test all this with FM5, sorry :) But it should
work, please tell me if it doesn't.
--
Mikhail Edoshin
Information Analyst
Skeleton Key
[EMAIL PROTECTED]
On Jul 18, 2007, at 6:59 PM, Nicholas Geti wrote:
I checked the article but I still have some questions:
1. The article creates a repeating field based on the statement,
"GET(CalculationRepetitionNumber)" I don't see a "GET()"
function in my version of Filemaker which is Pro 5.0
2. What is the variable, "CalculationRepetitionNumber"? Is this
a script or another field?
3. When the amount is placed in the repeating field, does it get
accumulated (i.e., added) to a running total or does it replace
the previous value?
4. My situation is more complex than simply converting month to
an index number. I have 13 different product types so I created
another field based on a case statement e.g.:
ProductCode=
CASE(Product Name="42399",1, Product Name="52233",2, etc)
This converts the Product Name to a numerical index. How would I
use this field in the definition of the repeating field? Does
the following definition of the repeating field make sense?
ProductRepeatField=
CASE(ProductCode, Qty)
where Qty is the amount field in the current record being read
from the input table.
----- Original Message ----- From: "Mikhail Edoshin"
<[EMAIL PROTECTED]>
To: <[email protected]>
Sent: Friday, July 13, 2007 10:51 AM
Subject: Re: Cross tabs possible?
On Jul 13, 2007, at 6:15 PM, Nicholas Geti wrote:
I need to create what I think can be called a cross-tab
report. I have a file of records; one column is product type.
I need to display a count of each product type as a column in
a report. There are thirteen possible types.
Is there a way to use SQL selects to count the types or do I
have to use brute force and create a table of thirteen
columns then scan the original table?
You can do this using repeating fields. The idea is that you
create a calculated repeating field with 13 repetitions, use
the product type to evaluate the corresponding repetition to 1
or 0 and them summarize the repetitions individually.
Check the following link for a sample:
http://edoshin.skeletonkey.com/2006/12/crosstab_report.html
Hope this helps,
--
Mikhail Edoshin
Information Analyst
Skeleton Key
[EMAIL PROTECTED]
