If statement use logic instead of aggregation

[zach_myt]

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?

[Benji_PaldiTeam · Accepted solution]

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

[Benji_PaldiTeam · Accepted solution]

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

[zach_myt]

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]

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

[david-h · Accepted solution]

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]

@david-h That works as well, thanks for the help!