Forum Discussion

zach_myt's avatar
zach_myt
Data Pipeline
12-26-2023
Solved

If statement use logic instead of aggregation

I want to use a simple if statement without using aggregation, just logical. How can I get this to work in an indicator widget:

IF([is_pec_broadcast_session] = 'Yes', [# of unique session_id],[# of unique user_id])

Simply, If is_pec_broadcast_session equals 'Yes' then count session_id, else count user_id.

I am receiving an error "Invalid Formula, is_pec_broadcast_session is missing aggregative function. I thought of using length but Sisense doesn't seem to have that built in. 

Is there a way to do this at the widget instead of going to change the model?

Dashboards & Reporting
  • Benji_PaldiTeam's avatar
    Benji_PaldiTeam
    12-27-2023

    Hi zach_myt ,

    Sisense always expect aggregation in widget formula. Also direct string comparison is not supported in widget formula. 

    But we can do it indirectly using aggregate function as below:

    In the below formula, if user selects [Condition] = 'New' it will return 10. In all other cases it will return 20.

    In your case, re-write the formula as below. Make sure to filter [is_pec_broadcast_session] to 'Yes'

    IF( MAX([is_pec_broadcast_session1]) = (MAX([is_pec_broadcast_session1]), [is_pec_broadcast_session]), [# of unique session_id],[# of unique user_id])

     

     

    Feel free to reach out if you have further questions, we're always happy to help 🙂
    [email protected] 
    Paldi Solutions, Number #1 Sisense Plugins Developer

  • david-h's avatar
    david-h
    01-13-2024

    Hi Zach,

    Benni's answer is very precise and accurate. If you are not comfortable with it, you can also try to translate your business needs in terms that Sisense complies with. Not always possible, but it looks like your use case here is compatible, especially with measured value (Creating Formulas Based on Criteria and Conditions (Filters) (sisense.com))

    You are looking to implement this KPI : IF([is_pec_broadcast_session] = 'Yes', [# of unique session_id],[# of unique user_id])

    Not sure of your complete data model, but it looks like this formula can work :

    ( [# of unique session_id],[is_pec_broadcast_session]) + ([# of unique user_id],[is_pec_broadcast_session])

    In the first term you add is_pec_broadcast_session as a filter to the YES value, and for the second term with the NO value.

    Let me know if this is compatible with your environment.

    Best,

5 Replies

Replies have been turned off for this discussion
  • Benji_PaldiTeam's avatar
    Benji_PaldiTeam
    Data Pipeline
    12-27-2023

    Hi zach_myt ,

    Sisense always expect aggregation in widget formula. Also direct string comparison is not supported in widget formula. 

    But we can do it indirectly using aggregate function as below:

    In the below formula, if user selects [Condition] = 'New' it will return 10. In all other cases it will return 20.

    In your case, re-write the formula as below. Make sure to filter [is_pec_broadcast_session] to 'Yes'

    IF( MAX([is_pec_broadcast_session1]) = (MAX([is_pec_broadcast_session1]), [is_pec_broadcast_session]), [# of unique session_id],[# of unique user_id])

     

     

    Feel free to reach out if you have further questions, we're always happy to help 🙂
    [email protected] 
    Paldi Solutions, Number #1 Sisense Plugins Developer

  • david-h's avatar
    david-h
    Cloud Apps
    01-13-2024

    Hi Zach,

    Benni's answer is very precise and accurate. If you are not comfortable with it, you can also try to translate your business needs in terms that Sisense complies with. Not always possible, but it looks like your use case here is compatible, especially with measured value (Creating Formulas Based on Criteria and Conditions (Filters) (sisense.com))

    You are looking to implement this KPI : IF([is_pec_broadcast_session] = 'Yes', [# of unique session_id],[# of unique user_id])

    Not sure of your complete data model, but it looks like this formula can work :

    ( [# of unique session_id],[is_pec_broadcast_session]) + ([# of unique user_id],[is_pec_broadcast_session])

    In the first term you add is_pec_broadcast_session as a filter to the YES value, and for the second term with the NO value.

    Let me know if this is compatible with your environment.

    Best,

  • zach_myt's avatar
    zach_myt
    Data Pipeline
    12-28-2023

    is_pec_broadcast_session is not a filter but a column from the data set which I am using to decide which ID's to count. I used your logic and seem to get correct results although I am still not entirely sure what it is doing. When I try MAX([is_pec_broadcast_session]) in a standalone widget I get nothing. Could you elaborate on why max comparing to itself works?

    I do get the correct results though, so thank you for that.

    • Benji_PaldiTeam's avatar
      Benji_PaldiTeam
      Data Pipeline
      01-02-2024

      When you add an aggregation, say MAX, to a string field the query will return a result of type string. It won't be displayed in widget, but it can be used to compare. We can see the actual result in the rawQueryResult in browser console (see screenshot below)

       

      Feel free to reach out if you have further questions, we're always happy to help

      [email protected] 
      Paldi Solutions, Number #1 Sisense Plugins Developer