AnsweredAssumed Answered

Aggregate functions

Question asked by eibcga on Dec 28, 2010
Latest reply on Dec 29, 2010 by eibcga


Aggregate functions


Hello all,

I'm a beginner using FMPA11v2 on a Mac and created a solution to analyze financial transactions in various ways.

Attached is my Relationships Graph, a "General Ledger" layout based on the LEDGER table, and a list of new Summary fields I added to that table so I can get some statistics from each account on the layout.  Also shown are other numerous LEDGER table layouts I use to analyze accounts in various ways (by the way, "FSA" stands for Financial Statement Area).  In addition to the existing aggregate functions I've added to the General Ledger layout using Summary fields (Total of, min, max, avg, stDev, count), which all work as intended, I would also like to add Variance.  Since there's no Summary field for Variance, I figured I would need to use the Variance function.

As you can probably tell, the General Ledger layout is a cross-tab report, where the Debit and Credit columns are based on IF statements that put the value from the Amount field in the Debit column if the amount is positive, and in the Credit column if the amount is negative.  I then use Summary fields to aggregate the Debit and Credit fields in various ways, all of which work as intended.  I also use functions to compute the average of each month or quarter on numerous layouts by having to use the GetSummary function (see attached).  Again, everything works great so far.

My problem is that I would now like to compute the variance of all the debit (and credit) values that appear in the Debit field (or Credit field), but there is no option for this in the Summary field type.  So, I created a new Calculation field in the LEDGER table called "var_dr" and use the formula, "VarianceP( debit )" with result as number, but all I get as a value in Browse mode for that field in the layout is a question mark? (and yes, the field is wide enough so show the full result). Aggregation works great using Summary fields, but can't get it to work using a function.  Any ideas?

I would also like to compute the same statistics (average, min, max, etc.) in various other layouts as was done in the General Ledger layout, but not sure how to tackle that, since there problems that arise when trying to use related fields in some functions, or the results just show question marks.  I know there must be a way to do this, but I'm missing something.  Again, any ideas to guide my way or some examples would be very helpful.

Thanks you.