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?
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 DeveloperHi 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,