cancel
Showing results for 
Search instead for 
Did you mean: 

If Statement within Advanced Filters?

simon_geoghegan
7 - Data Storage
7 - Data Storage

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:

simon_geoghegan_0-1739784417317.png

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

 

9 REPLIES 9

AssafHanina
Sisense Team Member
Sisense Team Member

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

AssafHanina_0-1740007081464.png

After applying the script

AssafHanina_1-1740007148984.png

Note: This is a sample solution that may not directly resolve the issue, but it provides a starting point for addressing it.

Best regards

 

Assaf

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:

simon_geoghegan_0-1740128859825.png

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:

AssafHanina_0-1740404404782.png

After Adding the Script, the User which select Today will get the following values:

AssafHanina_1-1740404618679.png

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

Assaf

DRay
Community Team Leader
Community Team Leader

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.

David Raynor (DRay)

simon_geoghegan
7 - Data Storage
7 - Data Storage

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

DRay
Community Team Leader
Community Team Leader

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?

David Raynor (DRay)

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

DRay
Community Team Leader
Community Team Leader

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?

David Raynor (DRay)

DRay
Community Team Leader
Community Team Leader

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?

David Raynor (DRay)