On 2008 Mar 28, at 12:59, Tsang, Tamara wrote:
Hello,
I’m currently trying to get some subtotals to work in FMP 8.5 and
I’m not sure if I need a calculation, script or something else.
I am trying to set up several calculation fields to total the
number of funds we are reporting on, as well as the subtotals by
active funds, by fund priority and completion, so I don’t have to
do a “find” each time and manually type it in.
Hopefully, my layout will look like the following chart (3 columns,
with totals at the bottom):
Total *Pri 1 Funds Total Active *Pri 1 Funds Completed *Pri
1 Funds
Total Pri 1 Funds Total Active Pri 1 Funds Completed Pri
1 Funds
Total Pri 2 Funds Total Active Pri 2 Funds Completed Pri
2 Funds
Total Pri 3 Funds Total Active Pri 3 Funds Completed Pri
3 Funds
Total Pri 4 Funds Total Active Pri 4 Funds Completed Pri
4 Funds
Total Club Funds Total Active Club Funds Completed Club
Funds
Total All Funds Total ALL Active Funds
Total ALL Completed Funds
I have the following fields and value lists:
Fund Status: Active/Inactive
Priority: *Pri 1, Pri I, Pri 2, Pri 3, Pri 4, Club
Completion Date: [date field] (Can I use *?)
Any ideas what type of calculation I would need to get subtotals
totals for each? I tried Count, but it wouldn’t let me specify
“active” or Priority type. I figure the Grand total columns can
add the fields above them, but getting those subtotal first has me
at a loss.
THANKS!
Tamara
Well, it looks to me like you need a bunch of calculation fields, 1
for each combination of characteristics you're looking for, and then
you show the total of THOSE.
Here are the 1st few to get you started:
Active1 = If ( Status = "Active" and Priority = "Pri 1"; Amount; "" )
Active2 = If ( Status = "Active" and Priority = "Pri 2"; Amount; "" )
Active3 = If ( Status = "Active" and Priority = "Pri 3"; Amount; "" )
...
Total1 = If ( Priority = "Pri 1"; Amount; "" )
...
Completed1 = If ( Status = "Inactive" and Priority = "Pri 1"; Amount;
"" )
...
ActiveAll = If ( Status = "Active"; Amount; "" )
CompletedAll = If ( Status = "Active"; Amount; "" )
BTW, I would strongly advise AGAINST having a field name that starts
with an asterisk, as that is almost guaranteed to screw up your
"Find" results in that field.
I'm assuming that "Inactive" means the same as "Completed". If that
assumption is wrong, some of the above formulas will need to be
rejiggered. If, however, it's correct, I suggest you replace the
manually enterable field "Fund Status" with a calculated field:
Fund Status = If ( IsEmpty ( Completion Date ); "active"; "inactive" )