Total of all AND total of set?

[fiveshorts]

Hi,

Is there an elegant way to have two fields, side by side on a Header; one showing a total of a field across ALL records in the file; and the other showing a total of the same field that occurs across a given found set?

In other words, a "total-of-all-records-regardless-of-found-set" and a "total-of-found-set-only"

Sorry if this is a dumb question...

[LaRetta]

Not a dumb question at all. One way would be to *create another copy of the table (maybe called All 'whatevers'). Join on anything other than unstored calc, global or container. And the join does NOT have to match in any way. Use the cartesian product (it's the last join option signified by the x).

Then create a calculation (result is number) = Sum ( All'whatevers'::thatField )

.. where thatField is the field you are summing.

* known as a table occurrence and NOT a different table itself)

That will give you the total-regardless-of-found-set. Then create a Summary field and select that same field (thatField). When placed in a part, it will give you a summary of that part. Place the fields side-by-side in the part you wish.

LaRetta

Edited January 5, 2008 by Guest

[fiveshorts]

Wow. Thank you for your speedy and comprehensive solution :-)

Much appreciated.

[LaRetta]

I wonder ... it doesn't make sense to want the same total in every part. If you mean you want the 'total for all records' to be all in that PART (regardless if they are in the found set), thne change that relationship to = and join whatever that field is in the part. For instance, if your report is grouped by Employee, join Employee = Employee. Then your Sum() calc will produce only THAT employee.

Sorry I almost misunderstood your need!

Edited January 5, 2008 by Guest

[fiveshorts]

Thanks :-)

I'm quite a table/occurrences noobie so I'm trying to get my head around the theory...

Here's the scenario:

My DB tracks financial trades made by a currency trader. Most of the work is done in a list view — one record per trade. Right now, the DB determines the risk represented by the trader's combined, active trades. It's all working great. But now I want to add the functionality of allowing the trader to "find" say, pending trades, or only trades that involve a particular currency and see (separately) what risk they carry as a subset, whilst all the time keeping an eye on the overall risk, regardless of the find. I'd like to keep this feedback in the header part as that never changes.

Hope that makes sense.

thanks for your help :-)

[David Jondreau]

How is the risk calculated now? In a parent Traders table, sub summary fields, or something else?

[fiveshorts]

Hi, yes, in a parent Trades table. 'Risk per trade' is expressed as a percentage of the current account balance and is either positive or negative, which translates to profit and risk respectively. I have a summary field on the Header that totals the positves and negatives to arrive at a total risk value for whichever found set is active. I want to be able to step back from the found set and always have an "all trades" total risk percentage value visible, right on the same header.

if that's possible... :-)

[Søren Dyhr]

Use the cartesian product (it's the last join option signified by the x).

Then create a calculation (result is number) = Sum ( All'whatevers'::thatField )

I have a reservation here, LeCates dissuade the use of aggregate functions, when dealing with larger sets of data. The Carthesian approach is fine, but since this ought to be a summary report shouldn't the field be made by a calc'field, but instead be a global field in the header or footer area, which is set by ...

GetSummary(cartesian::SummaryField;cartesian::SummaryField)

...during the course of sorting and getting into preview mode!

--sd

[LaRetta]

I love these kinds of trickery! Thanks! We could indeed eliminate the calculation (because it isn't being requested in Browse mode). But is there a way to use a break field of ALL? What break field would you use, Soren? I can't figure that out.

All I could see to do is use Set Field [ global ; Sum (cartesianSelf::thatField ) ]. But no extra calc ROCKS!

[Søren Dyhr]

Take a look at what GetSummary( does when the summary field is used as both parameters (both breaker as well as ...well summaryfield) in the function, watch that I didn't ditch your carthesian relation, but instead used it to get the summary dispite the found set.

It's the use of Sum( I talks against!

--sd

[LaRetta]

Okay, I think we missing each other here. Yes, undestood about GetSummary() but the request was not for a grand summary (which can be gotten just by placing the summary in a trailing grand summary, but for a TOTAL for the TABLE (if I understand correctly) regardless of the found set.

I see no way without using Sum().

But even so, it won't need to be on the layout so it should be very fast to grab, no? And it won't clog the field definitions needlessly either. Am I still misunderstanding? :crazy2:

Edited January 5, 2008 by Guest

[LaRetta]

Wait! I think I'm wrong! I had specified the current TO and not the cartesian! It appears to work as you've outlined!! YAY! And thank you! Very clever!

[Søren Dyhr]

You are I think, give it a stab in a single table ... or should I?

You have already defined the Summary field for the report right, then put a global field in the table as well and then make a cartesian relationship. When running the script that makes the summary report then just before getting into preview, use a single

Set Field[theGlobal;GetSummary(cartesian::SummaryField1;cartesian::SummaryField1)

Should at least in theory be faster than Sum(cartesian::transaction)

..................

Ah we cross posted, I see you got it!

--sd

Edited January 5, 2008 by Guest

[fiveshorts]

Er... hello... I think you wonderful super-brains have forgotten that I'm a mere mortal and have SO lost the plot... :-)

Is there any way to break this down into more layman's-speak?

Glad to have introduced a little "trickery" for you... :-)

[comment]

Why not just place the related summary field on the layout? Or, if you insist on storing it in a global,

Set Field [ gTotal ; AllTransactions::sTotal ]

[Søren Dyhr]

This is just a debate of the already suggested method, where I could see some scaling issues if the number of transaction to summarize on raises to a level near datamining.

I've included a small template, where some of the records of the entire set is omitted, to show that the global doesn't change despite ....

Execute the script just after omitting records!

--sd

plingplang.zip

[fiveshorts]

So the basic truth is that a find is performed on the current table occurrence, and the records within a duplicate of that occurrence will remain "un-found", so can be used for totalling?

[LaRetta]

Why not just place the related summary field on the layout?

Now there ya go again; always using the easy ways out...

As Michael says, just place the related summary directly into the header.

Edited January 5, 2008 by Guest

[LaRetta]

Hi fiveshorts,

I would explain it like this:

We created a second table occurrence of the same table. This is only a 'view' of that table and not another table itself. When we join the tables together using Cartesian operator, we are saying we want to summarize all data in the self-join table. You wanted the summary to appear whether a record was in the current found set or not. By basing it on a related table (and all records) ... and then by placing the summary (from that related TO) directly on your layout, you have your total. There is no need for script and no need for another calculation.

It doesn't have to do with the record which isn't being shown and that un-found record isn't do anything special. It's just that it's value is also being included because the summary from the related TO grabs them ALL regardless of whether they are in the found set or not. I believe that's what you are saying but I just wanted to clarify (or muddy) depending upon how much you understood. :wink2:

Edited January 5, 2008 by Guest

[Søren Dyhr]

Why not just place the related summary field on the layout? Or, if you insist on storing it in a global,

Even better, it achieves the same!

--sd

[fiveshorts]

LaRetta, Soren, Comment, DJ,

Thank you all for helping me with this . It's up and it's working :

Boy am I lovin' Filemaker. And this forum.