MoneyWorks Manual
Analyse (basis, sourceFile, searchExpression, breakDownFields, outputFields, fromDate, toDate [, kind] [, filterFunct])
Result Type: table of tab/newline delimited text
Definition: The Analyse function performs a transaction, job sheet, or ledger analysis using the parameters you specify. If possible, the result will be table-formatted (i.e. second breakdown level in columns), otherwise it will be list-formatted. The format is simplified to better allow programmatic manipulation of the result (especially using Head, Tail, Slice, etc). The first line of the returned table contains series names; and the first column contains categories/keys
basis: "Transaction" or "JobSheet", or (from v9.1.8) "Ledger"
sourceFile: The name of the table to source the top level search from (e.g. "Product", "Name", "Transaction", "Account", "Job")
searchExpression: a search expression to be applied to the sourceFile to find records to analyse. For transaciton or jobsheet basis, transactions of jobsheets related to the resulting record are what are analysed.
breakdownFields: text containing a comma-delimited list of the field names to analyse by, e.g. “Name.Category1,Name.Code". You may specify a breakdown calculation inside brackets instead of a field name. E.g. "Name.Category1,[Left(Name.Code, 2)]". For a ledger chart, the first breakdown field should usually be Transaction.Period (which breaks down by period)
outputFields: text containing a comma-delimited list of output values, where 1 = count, 2 = qty, 3 = Net, 4 = GST, 5 = Gross, 6 = Unit Price, 7 = Extended 8 = Cost Price, 9 = Margin, 10 = Discount, 11 = % Margin. (see the output value popups in the Analysis editor). e.g. "2,3" will give you Qty and Net columns. For a single output field, you can follow the field number with a "#", which denotes that the resulting table should include a total.
fromDate: a start date or start period for the range of transactions or job sheet entries to analyse.
toDate: an end date or end period
kind: (optional) character codes denoting what kind of analysis and also how to render signs or debits and credits. e.g. "IEU" denotes analyse Income and Expenses, including unposted.
I = Income; E = Expense; B = Billed; U = Unbilled (or Unposted). "I" or "IE" will render natural credits as positive; use for analyses including income or liabilities. "E" will render natural debits as positive (should be used for analysing expenses or assets on their own)
Default is "IEUB"
filterFunct: (optional) a filter function to filter out detail lines or job sheet entries that you don't want to analyse.
Examples:
analysis = Analyse ("Transaction", "Name", "customertype <> 0",
"Transaction.NameCode,Transaction.Period", "3#",
PeriodOffset(CurrentPer, -7), PeriodOffset(CurrentPer, -1))
Analyses transactions for Names. SearchExpression yields customers. Breaks down by NameCode and Period. Only one output field (Net), so will get table format with Periods as column headings. Analyses the last 7 periods. The result can be further refined:
top = head(sort(analysis, -1, 1, 1, 1), 6, "\n")
Gets the top 5 Names of the Analysis by total value over all periods (total is rightmost column, hence sorting by column -1). A descending, numeric sort which skips the first (heading) line brings the top 5 customers. If we want to chart the result (in the Chart part of a custom report) with a period series for each name, we would need to transpose it, and get rid of the total column (last line after transposing)
tochart = head(transpose(top), -1, "\n")
Running Analyse() on the server:
Since Analyse() runs on the client, it can be time consuming for complex analyses or large data sets because it makes many individual database requests. In MoneyWorks 9.1.8 and later you can use the Val() function with the new runOnServer option to run your analyse function on the server for much better performance. If you are calling Analyse() in a report that will run on the server, this is not necessary.