cancel
Showing results for 
Search instead for 
Did you mean: 

If statement use logic instead of aggregation

zach_myt
10 - ETL
10 - ETL

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?

2 ACCEPTED SOLUTIONS

Benji_PaldiTeam
11 - Data Pipeline
11 - Data Pipeline

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.

Benji_PaldiTeam_1-1703687714665.png

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])

Benji_PaldiTeam_2-1703688148330.gif

 

 

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

View solution in original post

david-h
9 - Travel Pro
9 - Travel Pro

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,

View solution in original post

5 REPLIES 5

Benji_PaldiTeam
11 - Data Pipeline
11 - Data Pipeline

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.

Benji_PaldiTeam_1-1703687714665.png

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])

Benji_PaldiTeam_2-1703688148330.gif

 

 

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
10 - ETL
10 - ETL

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.

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)

Benji_PaldiTeam_0-1704211518646.png

 

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
9 - Travel Pro
9 - Travel Pro

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
10 - ETL
10 - ETL

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