This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Cookie Policy. Click Preferences to customize your cookie settings.
on - edited on by
DRay
Download: Financejs.zip
Introduction
This plugin was created to enable additional financial calculations within a pivot table. The initial version of this plugin supports IRR, XIRR, & NPV functions that match what you would expect in Excel/Google Sheets. This plugin works by making additional queries against Elasticube to gather data required by these functions, and calculates a new result to insert into the table
An important note is that this implementation is just an example of how to create additional formulas. All business logic for deciding what functions are supported is contained in finance.js and functions.js.
Steps
The following steps will walk through the process of adding the new plugin.
STEP 1 - ADD THE PLUGIN
Download the attached .zip file, and extract it to the following path "[Sisense installation path]\PrismWeb\Plugins". If you are using version 7.2 and higher unzip the contents into your C:\Program Files\Sisense\app\plugins\ folder.
STEP 2 - CREATE THE PIVOT
The main challenge with functions like these is that they require more data, than just what's displayed in the pivot table. In order to specify the extra dimensions needed, you can create a multi-pass aggregation function, that this plugin will use to generate a new query. The screenshots below show how to create a formula that includes the time dimension needed to get supporting data used by the functions.
Formula for XIRR or IRR: Requires a date dimension (month end) as well as an aggregation (sum of payments)
Formula for NPV: Requires a date dimension (month end), a discount rate ( 0.1 for 10% ), as well as an aggregation (sum of payments)
Now that the formula includes where to find the data, click on the settings menu for the value and select the Finance function you want to apply.
Same formulas being used in Google Sheets
NOTES
- This sample has been confirmed working on Sisense version 7.1, and should be backwards compatible with previous version
- This plugin supports only pivot tables with rows, not dimensions as columns
- This plugin does not support exporting to PDF, XLS, or CSV
- Updated June 26, 2018: fixed an issue w/ dashboard filters not being applied
