Custom fields can be created and calculated based on existing data
Calculated fields
To add a calculated field
Right-click on Table name
Examples and examples
This year's figures to date
YTDate = ToDecimal(Iif(IsThisYear([Date]), [Amount], 0))
Last year's figures to date
LYToDate =ToDecimal(Iif(IsThisYear(AddYears([Date], 1)) And AddYears([Date], 1) <= Today(), [Amount], 0))
Index Index
= ToDouble(Sum([NetAmount]) / Lookup(Sum([NetAmount]), First())) * 100
Here calculated on an existing field
Now the calculated field can be used in a "Grid", for example. As shown below, the value is now displayed for each account type.
You can now filter on the value instead of the text and the Dashboard can be used in multiple languages.
The filtered grid is shown here.
Examples and examples
This year's figures to date
YTDate = ToDecimal(Iif(IsThisYear([Date]), [Amount], 0))
Last year's figures to date
LYToDate =ToDecimal(Iif(IsThisYear(AddYears([Date], 1)) And AddYears([Date], 1) <= Today(), [Amount], 0))
Index Index
= ToDouble(Sum([NetAmount]) / Lookup(Sum([NetAmount]), First())) * 100
Here calculated on an existing field
Shifting the accounting year
General formula that will apply to all customers. Requires the table "CompanyFinanceYearClient" to be active. iif(GetMonth([FromDate])=1,AddMonths([Date],GetMonth([FromDate])-1),AddMonths([Date],GetMonth([ToDate])*-1)) Formula that will apply to a specific financial year Fiscal year: AddMonths([Date],Offset) Offset will typically be 3 for a 30/9. This turns 1-10 into 1-1 If you want the financial year to be written as year1/year2, you can do this: iif(GetMonth([FromDate])=1,ToStr(GetYear([Fiscal Year])), Tostr(GetYear([Fiscal Year])) +'/' + ToStr(GetYear([Fiscal Year])+1 ))Converting exchange rates
Use the formula below: GetExchangeRate( 'EUR', 'DKK',[Date] ) Then an amount can be converted to another currency. Please note that [Date] must be entered, but that today's date is always used in the currency conversion. Explanation of the formula. GetExchangeRate('"To currency"', '"From currency"',"Always today's date regardless of what is written" )Using "e-nums" values instead of texts in calculated fields.
Version-93 If a dashboard is to be used in multiple languages, it is not appropriate to use texts in e.g. filters. If texts are used, the dashboard can only be used in one language. It is therefore possible to extract the "e-nums" values. This is done in the following way. Load the tables, Create a calculated field. Select Functions/All and select the table and field where "e-nums" should appear as values. Here the [AccountType] field is translated GetAppEnumIndexGLAccountClient([AccountType], 'AccountType')
Now the calculated field can be used in a "Grid", for example. As shown below, the value is now displayed for each account type.
You can now filter on the value instead of the text and the Dashboard can be used in multiple languages.
The filtered grid is shown here.