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
Purpose:
To create a product aggregation in Sisense (multiplies all values together, similar to the way a sum function adds all values together).
Situation:
A financial use case has a table with relative daily gains/losses for certain securities (think stocks; if yesterday it closed at $100 and today it closed at $101, then there was a 1% gain today). The goal is to calculate the % change over any given timeframe from the start to the end or in other words, multiply each day’s value (+ 1), or in Excel land Product(1+value).
Approach:
There is no product function in Sisense (it is not a common BI function), so we needed to break down the function into its fundamental parts. The end result, which assumes we have all positive values (applicable for this use case) looks like this in SQL:
Note: the base of the log must match the base of the exponential
Resolution:
- Start with the table shown below. The XLE column is the relative gain or loss for that day and the XLENorm = XLE + 1 (now they will all be positive). The idea here is to take the product or in other words multiply all values form the XLENorm field together to get an aggregate relative value over any given date range.
2. Create a new widget with the following function.
3. This is similar to what was shown above, but the LN() function takes an aggregation. Therefore we make the input for LN the sum of XLENorm, and in the SUM() we add a multi-pass by date. It will roll up to whatever you like however I suggest duplicating the Date field so you can drill all the way down to the dates as well (requirement of multi-pass).
4. Finished product:
Notes & Insight:
- This could have several potential applications for financial or other use cases (compounding interest rates, etc.)
- As noted before this particular calculation assumes a positive value which was fine for our purposes. As outlined in the document there are some other more encompassing formulas which will work for zero, null and negative values. These should be possible in Sisense but would require some nested conditional statements or perhaps some smart use of filters, absolute values, counts of negatives (aka the product of an even number of non-null or zero values is positive, and the product of an odd number of non-null or zero values is negative) and possible Boolean algebra
- A product function is not available in most other BI tools, nor as a standalone function in SQL
