on - edited on by
DRay
Users could sometimes want to compare performance on a particular time period vs the immediately preceding period. Sometimes those periods are not of a common size like months or quarters. It could be a campaign that lasts a few days, or an arbitrary date range based on topics trending on social media. In those cases, you would want to select any time range and be able to compare to a previous period of the same arbitrary size, and be able to change that time range and see the comparison adjust dynamically.
Challenges
- In Sisense, the RANGE function receives literal numbers, but it doesn’t accept numeric expressions (like Count([Days in Date]).
- Sisense has last, next and offset operators for filters, but no out of the box ways to adjust them according to the number of members in a filter.
Solution:
By using the Javascript API and in formula filters, we can make a formula go back the number of days in the filter, and also apply an offset of the same number of days, to get the same data for the preceding time window.
Example:
We want to be able to get the number of orders in an N day date range, and the growth vs the previous N days. For example, if the filter is 2/11/2020 to 2/20/2020, we want the growth vs the 2/1/2020 to 2/10/2020 period. We'll use an indicator widget for this, with the next formulas:
- Value: Count([OrderID)
- Secondary: (Count([OrderID]) - (Count([OrderID]),[Days in Date]))/(Count([OrderID]),[Days in Date])
And set the formula filters to one of the predefined “Last N” options:
To learn about filtering in formulas, check the documentation: Document Here
Finally, open the Edit Script editor for the widget, and enter the following script. The script will search for formula filters that use the table and column specified at the beginning of the script (and use the last operator), and replace the count and offset with the number of members in the filter. If the calendar was used to set a days filter, it will calculate the difference in days to come up with the number of members:
If you change the filter to a different date range, the filters in the growth formula will adjust dynamically to the new time window.
Script:
var TABLE = "Date";
var COLUMN = "Date";
widget.on("beforequery", function(scope, widget) {
// getting item
var item = widget.query.metadata.find(function(i) { return i.panel === "scope" && $$get(i, "jaql.table") === TABLE && $$get(i, "jaql.column") === COLUMN });
if (!defined(item)) {
console.log("DynamicLastX: Can't find a filter item with '" + TABLE + "." + COLUMN +"' dimension. Aborting.");
return;
}
var calFilter = false;
var calCount = 0;
var dateTo = null;
// getting member
var members = $$get(item, "jaql.filter.members");
if (!defined(members)) {
console.log("DynamicLastX: No item filter. Assuming calendar was used.");
calFilter=true;
var dateFrom = new Date(item.jaql.filter.from + "T00:00:00");
var dateTo = new Date(item.jaql.filter.to + "T00:00:00");
var diffTime = dateTo.getTime() - dateFrom.getTime();
calCount = diffTime / (1000 * 3600 * 24) + 1;
}
var formulaFilters = [];
var filterMember = 0
var filterCount = 0
if (!calFilter) {
filterMember = members[0];
filterCount = members.length
} else {
filterMember = item.jaql.filter.to + "T00:00:00";
filterCount = calCount;
}
widget.query.metadata.forEach(function(item) {
if ((item.source === 'value' || item.source === 'secondary') && item.jaql && item.jaql.context) {
for (var lmnt in item.jaql.context) {
if (item.jaql.context[lmnt].table === TABLE && item.jaql.context[lmnt].column === COLUMN && item.jaql.context[lmnt].filter && item.jaql.context[lmnt].filter.last) {
formulaFilters.push(item.jaql.context[lmnt].filter);
}
}
}
})
if (formulaFilters && filterMember) {
formulaFilters.forEach(formulaFilter => {
formulaFilter.last = {
offset: filterCount,
count: filterCount,
anchor: filterMember
};
});
}
});
