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"
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:
When invoked from a script, Analyse() will only run on the client, which can be time consuming for complex anlayses or large data sets. To overcome this, there is a built-in MoneyWorks report called ~serverside_analyse_vt which can be invoked using doReport(). The report parameters are the same as Analyse, except all must be specified.
let params = createArray()
let params["basis"] = "transaction"
let params["sourceFile"] = "product"
let params["searchExpression"] = "category1=`B@`"
let params["breakDownFields"] = "name.category1"
let params["outputFields"] = 3
let params["fromDate"] = '1/1/23'
let params["toDate"] = '31/12/23'
let params["kind"] = "I"
let params["filterFunct"] = ""
let r = DoReport("~serverside_analyse_vt", "", "", "prefer_remote=1", "override_doc_custom=1", params)
let r = replace(r, char(#B), "\t")
A critical difference is that the report returns a table of text delimited by vertical tabs, and not tabs. Hence the last line in the above example, which restores the tabs character.
Several sequential analyses can be run in the one report by overloading the "basis" parameter with a newline delimited list of analyses (in this case the other parameters can be omitted). Each line should start with a #, followed by an identifier for the analysis, then all the remaining parameters separated by tabs. For example, below we do two analyses from period 301 to 312
let analyses = ConcatAllWith("\t", "#bycat1", "transaction", "product", "category1=`B@`", "name.category1", 3, '1/1/24', today(), "I", "") + "\n"
let analyses = analyses + ConcatAllWith("\t", "#bycode", "transaction", "product", "category1=`B@`", "name.code", 3, PeriodOffset(CurrentPer, -3), CurrentPer, "I", "") + "\n"
let r = DoReport("~serverside_analyse_vt", "", "", "prefer_remote=1", "override_doc_custom=1", createArray("basis", analyses))
let r = replace(r, char(#B), "\t")
After replacing the result with tabs, we get:
#bycat1 1 North 26764.94 South 28986.65 #bycode 1 AUTUMN 313.05 BROWN 10063.80 GREEN 6782.61 ROSE 6849.29 SMITH 174.13 SPRING 11298.95 SUMMER 4055.80 WHITE 12757.60 WINTER 3456.36
Each table can be picked out by parsing the text between the # identifier. The second value is the number of breadown fields, which you will need in parsing the result.