cancel
Showing results for 
Search instead for 
Did you mean: 

Edit Widget Script Sisense to display NA for specific condition

Lisha
7 - Data Storage
7 - Data Storage

I want to add a custom script for my widget using this option

image1.png

 

 

 

 

I have a calendar filter on my dashboard like below

image2.png

 

 

 

It is being carried forward to the widget like so

image3.png

 

 

 

Our fiscal year is Oct to September.
Now, my requirement is that whenever user selects date range spanning more than 1 fiscal year(eg. 1st September 2021 to 30th October 2021), the widget (gauge indicator) should display NA for both value and secondary measure.

image4.PNG

Earlier, my secondary measure was empty, so I just added a measure there to count unique number of fiscal years and added the below code in widget script.

widget.on('render', function (se, args) {
if(se.queryResult.secondary.data > 1)
{
se.queryResult.value.text = "N/A";
}
});

But now, I have another measure in the secondary field so cannot do this anymore. For this, I was thinking of adding this condition in the widget script by somehow using the values from the date filter. I've searched the sisense documentation but am unable to find ways on how to access the filter values selected by user.

Please help me with the script for this.

1 ACCEPTED SOLUTION

harikm007
12 - Data Integration
12 - Data Integration

Hi @Lisha ,

Just trying to understand TOP 1 filter (date field converted to integer) - when your secondary measure was empty, you were using a measure to count unique number of fiscal years and added the a script, right? Does it still working if you replace your current secondary measure with previous one?

 Also please try 'ALL' function with integer version of date field in the formula.

if(([# of unique Years in Date], ALL([Integer Date Field]))>1, NULL, SUM([Actual]))

Another solution would be to bring the TOP 1 filter (date field converted to integer) inside the formula and use CASE  statement

-Hari

View solution in original post

6 REPLIES 6

harikm007
12 - Data Integration
12 - Data Integration

Hi @Lisha ,

Please try this formula:

if([# of unique Years in Date]>1, NULL, SUM([Actual]))

harikm007_0-1653980269644.png

Here is the result when user selects date range spanning across multiple fiscal year:

harikm007_1-1653980489931.png

If you need to remove '#' from the result, use below script:

widget.on('processresult', function(se, ev){
	if(ev.result.value.text == '#N/A')
		ev.result.value.text = 'N/A'
	
	if(ev.result.secondary.text == '#N/A')
		ev.result.secondary.text  = 'N/A'
})

-Hari

 

Lisha
7 - Data Storage
7 - Data Storage

Hi Hari,

I have already tried doing this using CASE WHEN condition. However, I have another widget filter which takes the MAX of date (converted to integer) and retrieves the top value by ranking.

Because of this filter, the calculation for CASE WHEN (or if as you suggested) part is also getting affected and not giving desired result.

If I remove the widget filter for max date value, the solution you suggested will work but that will not give us correct result for our metric.

Which is why I was thinking of handling this in the widget script entirely. 

 

Lisha
7 - Data Storage
7 - Data Storage

Hi @harikm007 ,

Have any solution for this issue ?

Thanks

harikm007
12 - Data Integration
12 - Data Integration

Hi @Lisha ,

Just trying to understand TOP 1 filter (date field converted to integer) - when your secondary measure was empty, you were using a measure to count unique number of fiscal years and added the a script, right? Does it still working if you replace your current secondary measure with previous one?

 Also please try 'ALL' function with integer version of date field in the formula.

if(([# of unique Years in Date], ALL([Integer Date Field]))>1, NULL, SUM([Actual]))

Another solution would be to bring the TOP 1 filter (date field converted to integer) inside the formula and use CASE  statement

-Hari

Lisha
7 - Data Storage
7 - Data Storage

@harikm007 

Even if I replace the secondary measure with the previous one and add the previous widget script, it is not working since somehow the dashboard filter (top rank) is affecting it. It is doing some sort of inner join and applying this top rank condition everywhere, so even if I give date range spanning 2 fiscals (eg. 1sep 2021 (fy2021) to 30oct 2021(fy2022); it takes the top one i.e. 30oct 2021(fy2022) and our logic fails as count becomes < 1.

As far as bringing the TOP 1 filter inside the formula and using CASE is concerned, have tried that as well; it doesn't work either.

I haven't tried using the 'ALL' function. I'll see if that works and let you know. Thanks for the suggestion.

 

Lisha
7 - Data Storage
7 - Data Storage

Hi @harikm007 ,

It worked perfectly using the 'ALL' function. Thank you so much for your help and efforts.