cancel
Showing results for 
Search instead for 
Did you mean: 
Community_Admin
Community Team Member
Community Team Member

Question:

Currently, when creating a formula at the widget level, you can filter the formula but by a text field only. How can I filter the formula by the numeric field?

 For example, this is helpful when creating a percent of total calculation -- I want to see all my companies with a revenue > $1,000,000 as a percent of the total number of companies.

Community_Admin_0-1634253893532.png

Answer:

You can also use a numeric field for the filter part of the formula, but it will not be a filter on the aggregation. There can be following solutions:
1) Use an advanced filter based on aggregation. The JAQL can be generated by Sisense or you can have a shortcut to your result. For instance, you can add a ranking filter on a dimension based on a formula (click fx) and then check the generated JAQL.
Also add a filter in the widget advanced editor by using the filter icon on the top right corner of a value. You can then select all values above a certain criteria. The filter will be applied to the last dimension you add in the widget.
2) Use a multi pass aggregation. If you want your widget to be applied on client with above $1M revenue then you can use a formula like this one : SUM ( [CLIENT (all items)], IF( [REVENUE] > 1000000, 1, 0) )
You can then divide by a COUNT([CLIENT]) for instance.
I find this second solution easier to manage, especially when combined with other filters.
If you need more info on multi pass aggregation, you can check this documentation page : https://documentation.sisense.com/latest/creating-dashboards/using-formulas/build-formulas.htm#gsc.t...

 

Version history
Last update:
‎10-14-2021 04:25 PM
Updated by:
Contributors
Community Toolbox

Recommended quick links to assist you in optimizing your community experience:

Need additional support?:

Community Support Request