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.

Any help will be appreciated.

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

6 Replies

Replies have been turned off for this discussion
  • 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. 

       

      • 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