If Statement within Advanced Filters?

[simon_geoghegan]

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

[AssafHanina]

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)

1. Add a date filter on top of the dashboard (first filter)
2. Open Dashboard Menu and Click on Edit Script 
3. 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

[simon_geoghegan]

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

[AssafHanina]

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

[DRay]

Hi @simon_geoghegan,

I’m following up to see if the solution offered by @AssafHanina worked for you.

If so, please click the 'Accept as Solution' button on their post. That way other users with the same questions can find the answer. If not, please let us know so that we can continue to help.

Thank you.

[simon_geoghegan]

Thanks @AssafHanina, @DRay  - apologies for the delay, I'm still looking at how I can implement this at my side.

[DRay]

Hi @simon_geoghegan,

If you like, I can get the ball rolling on connecting you with a Sisense technical resource who will work with you directly to help get this implemented. Would you like me to do that?

[simon_geoghegan]

Hi @DRay - that would be hugely beneficial and appreciated, thanks

[DRay]

Hi @simon_geoghegan,

Ok, I'm happy to do that. 

In order to connect you with someone, we need to know what company you work with. Can you let me know who you work with, or reach out to your Sisense account team about getting a technical resource for you?

[DRay]

Hi @simon_geoghegan,

Ok, I'm happy to do that. 

In order to connect you with someone, we need to know what company you work with. Can you let me know who you work with, or reach out to your Sisense account team about getting a technical resource for you?