Edit Widget Script Sisense to display NA for specific condition

[Lisha]

I want to add a custom script for my widget so that whenever user selects date range spanning more than 1 year, the widget (gauge indicator) should display NA for both value and secondary measure.

Any help will be appreciated.

[harikm007]

Hi @Lisha ,

Please try this formula:

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

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

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]

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]

Hi @harikm007 ,

Have any solution for this issue ?

Thanks

[harikm007 · Accepted solution]

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]

@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]

Hi @harikm007 ,

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