Forum Discussion

Lisha's avatar
Lisha
Cloud Apps
05-30-2022
Solved

Edit Widget Script Sisense to display NA for specific condition

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. ...
Dashboards & Reporting
Finance Analytics
  • harikm007's avatar
    harikm007
    06-02-2022

    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

harikm007's avatar
harikm007
Data Warehouse
05-31-2022

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's avatar
    Lisha
    Cloud Apps
    05-31-2022

    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's avatar
    Lisha
    Cloud Apps
    06-02-2022

    Hi harikm007 ,

    Have any solution for this issue ?

    Thanks

    • harikm007's avatar
      harikm007
      Data Warehouse
      06-02-2022

      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's avatar
        Lisha
        Cloud Apps
        06-02-2022

        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.