Financial Functions

[intapiuser]

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