cancel
Showing results for 
Search instead for 
Did you mean: 
intapiuser
Community Team Member
Community Team Member
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

          };

                               });

 }

});
Rate this article:
Comments
Astroraf
10 - ETL
10 - ETL

@intapiuser does this still work? When trying this code, it didn't seem to work for me. 

DRay
Community Team Member
Community Team Member

Hi @Astroraf

Can you provide some details around the issue you are having?

Astroraf
10 - ETL
10 - ETL

Hi @DRay ,

 

I set up two widgets, one of which implements the code above and the other that does not. But when I implement the code I do not see a difference in the numbers as expected. I direct the code to the right tables within my instance, in my code, but to no avail. 

DRay
Community Team Member
Community Team Member

Hi @Astroraf.

Thank you. I'll have someone dig into this and see if the code is still valid. What version of Sisense are you using?

Astroraf
10 - ETL
10 - ETL

L2024.2.0.78

DRay
Community Team Member
Community Team Member
Astroraf
10 - ETL
10 - ETL

Will look into it and report back

DRay
Community Team Member
Community Team Member

Hi @Astroraf

I talked with @AssafHanina and he provided this:

"The issue here is having 2 periods such as last 2 months.
The offset should be last 0-1 compare to 2-3 but it's compare 0-1 to 1-2 PastPeriod function has been deprecated (although still in use) but will end up with the same output. Filtered measure can provide the flexibility with 2 date filters and provide wider option for the user to compare between different time periods rather than Fixed time periods."

Does that help?

Version history
Last update:
‎02-05-2024 12:53 PM
Updated by:
Contributors