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 or job sheet 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: a file name (e.g. "Product", "Name", "Transaction")
searchExpression: a search expression to be applied to the source file to find records to analyse.
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)]"
outputFields: text containing a comma-delimited list of output values, where 1 = count, 2 = qty, 3 = Net, 4 = GST, 5 = Gross etc. (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 column.
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. e.g. "IEU" denotes analyse Income and Expenses, including unposted.
I = Income; E = Expense; B = Billed; U = Unbilled (or Unposted)
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.