Hi All,
First time posting here and hoping for some advice around advanced filters if possible please.
I currently have this filter which gives me results based upon the previous createddatetime:
Unfortunately, this scenario is not always relevant - due to some departments being closed at weekends, there is nothing displayed when the report is ran on a Monday morning.
Is there any way of applying IF logic within advanced for the following scenario?
If dayoftheweek = 1 (i.e. Monday)
count: 3, offset: 1
else
count:1, offset: 1
Effectively, I want to show Friday, Saturday & Sunday data when the current dayoftheweek is Monday, otherwise I just want the previous days data.
I've had a search around the knowledge base but couldn't find anything (however i'm relatively new to Sisense so it might just be that I've not understood what some of the forum posts meant!)
Any help greatly appreciated.
Regards,
Simon
hey @simon_geoghegan ,
From the description shared in the post, it's sounds like the needs is to be able to change the JAQL once the filters apply, based on the day of the week.
First of all, it's recommended to have a Date Dimension in the data model which contain columns such as Day of Week (number) and Day Name.
Sisense doesn't support natively if Statement within dashboard filters however the following can be achieved using the native dashboard class API that Sisense provides.
This, Allow to run a Java script within a dashboard script and to change the Filter Jaql while the Filters changed.
Here is an example of accessing the Sisense JAQL (Assuming there's one Date filter in the dashboard)
- Add a date filter on top of the dashboard (first filter)
- Open Dashboard Menu and Click on Edit Script
- Paste the script
dashboard.on('filterschanged', function(dashboard) {
let filters = dashboard.filters.$$items;
if (filters.length > 0) {
let filter = filters[0].jaql; // Get the first filter
if (filter?.filter?.last) {
console.log("Before update:", filter.filter.last);
// Toggle the count value: If 1 → set to 3, else set to 1
filter.filter.last.count = filter.filter.last.count === 1 ? 3 : 1;
console.log("After update:", filter.filter.last);
// Apply the updated filter without triggering a full dashboard refresh
dashboard.filters.apply();
}
}
});
The script is an example of how to update the jaql.last.count value using the 'filteredchanged' event and refresh the dashboard with the correct values based on the script.
The Original selection by the User
After applying the script
Note: This is a sample solution that may not directly resolve the issue, but it provides a starting point for addressing it.
Best regards
Hi @AssafHanina ,
Thanks for your reply and solution.
I am trying to understand how this would work for me (as my understanding of Sisense is still of a relative beginner).
Do I need to add a date filter as you've suggested - like so:
And then the code will automatically change this to 1 or 3 days based upon a dashboard refresh (which is set to daily) ?
Regards,
Simon
Hey @simon_geoghegan ,
The Code Should change the related Offset based on Day Granularity selection.
In your example, the Filter of Date Before the script present the following:
After Adding the Script, the User which select Today will get the following values:
You might need to adjust the script a bit according to the use case, but it's ready to be tested (Copy the Script to the dashboard and filter on Today)
Best Regards
Assaf
