Also see: [Video Tutorial] Formulas

To make your reports more effective, you may need to use formulas for calculating various business indicators, e.g., % of customer credit used, exchange rate balance, % of the invoice paid, age of invoice (dispute, task), % of the total balance per bucket, etc. 

See below for some examples of formulas that might be helpful in making your reports more functional.

• % of customer credit used = ERP Balance / Credit Limit (result shown in %) - Customer datasource
• Exchange rate balance = Balance * Exchange Rate - Invoice datasource
• % of the invoice paid = measure Invoice Paid / measure Invoice (result shown in %) - Invoice datasource
• Invoice (dispute, task) age = (Today() - Due Date) 
• What % of Total Balance is a bucket = Ageing(1-30) / ERP Balance (result shown in %) -  Customer datasource

Formulas are displayed in the grid as separate columns, with standard behavior (can be hidden, moved, and sorted). Formula column fields contain the calculated result:

How to add a formula to your report grid

• when creating a new report, add a formula column to your report grid at a step of selecting columns.
• when editing an existing report, add a formula column to your report grid in a column picker ("Select Columns" button):

Click + Add Formula:

"Manage Formula" window pops up, where you can create the required formula:

1. Enter the Formula Column Name as it will be displayed in the report grid header.

2. Select Field #1: 

• FUNCTIONS: Today
• FORMULAS: simple formulas columns results (if any)
• DATASOURCE FIELDS: column data from the report source (numbers, dates)

3. Choose an arithmetic Operator:

• addition (+)
• subtraction (-)
• multiplication (*)
• division (/) 

4. Select Field #2:

• FUNCTIONS: Today
• FORMULAS: simple formulas columns results (if any)
• DATASOURCE FIELDS: column data from the report source (numbers, dates)

5. Set the number of decimals the result will be rounded to - from zero (whole numbers) to six (two by default, e.g. 3.65) (where applicable)

6. Enable the "Show results in %" checkbox, if needed (where applicable)

7. Click SAVE.

Remember that you cannot mix data types within one formula:

• if Field #1 is a numeric value, Field #2 must be also numeric
• if Field #1 is a date-type value or "Today" function, Field #2 must also be date-type or "Today" function

After the formula is created, you can see the checkbox near its name in the "Add report" or "Select columns" window: when enabled, the formula column is displayed in the grid.

In the show-more menu on the right of the formula name, you can find the actions available:

Edit Formula

You can edit your formula both at a step of creating a report ("Add report") and later, e.g. during the selection of columns in the column-picker.

Upon clicking on "Edit", a "Manage Formula" modal pops up, where you can make the necessary changes. Click SAVE. Remember that your changes may impact report data, filters, charts if they use the formula. The dependent formulas may also be affected.

Use case:

If you edit the formula so that it involves changing the result type (e.g., from decimal to integer), and this formula is already used in a filter with decimal limit (e.g., 0.3), when setting up another filter for the same formula, you will not be able to enter a decimal limit, only integer (0, 1, 3, etc.).

Delete Formula

To delete a formula click on "Delete" in the show-more menu to the right of its name in the "Add report" or "Select columns" window.

Exporting Formulas to Excel

Having added a formula to the grid, you can export the grid with formula results to Excel even without having saved the report: click the "Export to Excel" button above the grid.

However, note, that if you refresh the page without saving the report with the formula, the formula column will not be included in the Excel file during subsequent exporting.

Scheduling Reports with Formulas

Formula columns are included in the grid of scheduled reports as well. For the details of the "Report Schedule" functionality see https://yaypay.helpjuice.com/en_US/reporting/reporting_add-report.

Simple and Complex Formulas

While simple formulas operate grid columns data explicitly, complex formulas incorporate simple formula(s) as one or both fields (operands).

After you have created a simple formula, you can select its result to be used in a complex formula field: in this case, simple formulas will be displayed under the FORMULAS section in the dropdown list of Field #1 and Field #2:

The maximum number of nested formulas starting from the parent formula is five. 

Also, cycling validation is implemented: when editing a parent-level formula you cannot use low-level formulas, containing it, as fields.

Ability to filter formula results

REPORTS TAB

You can apply a filter to the column with the formula results within your report: click Filter > Add filter (or Add Group for several items) and select the formula(s) under the REPORT FORMULAS block.

Then enter the condition and the value for the column to be filtered by and click APPLY.

The displayed formula results will be filtered with respect to the given value, and the used filter will be shown as a summary:

To save the filter as a preset, click "Save as preset", enter the name and click SAVE.

DASHBOARDS TAB

In the DASHBOARDS tab the formula filtering functionality is the same except for the filter being used within each widget (chart or grid) separately:

For a general description of filters and presets functionality in the Reporting module, refer to https://yaypay.helpjuice.com/en_US/reporting/798514-filters-and-charts.

Ability to use formulas in charts and dashboard widgets

When adding a new chart to your report or editing an existing one, you can use the created formula value as an x-/y-axis parameter (for column and line charts) or as a dimension/measure (for pie chart).

Click Add Chart (or Edit button in the existing chart Show More menu) and select your formula to be used as a respective axis or field.

You can also add a widget containing a formula to your dashboard in the Reporting module: go to the DASHBOARDS tab > click Add Widget and select the respective report grid or chart with the formula from the dropdown list.