Filter a Formula by a Number

Comments

1 comment

  • Avatar
    David Hertzberg

    Hi Jessica,

    You can also use a numeric field for the filter part of the formula, but it will not be a filter on the aggregation. And actually this is why this is not so a trivial question.

    I usually get to the result you describe by one of these two possibilities :

    1) Use an advanced filter based on aggregation. This can get tricky though ... But in some cases, 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.

    Or if it fits your use case, 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 agregation. 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.tab=0

    Best,

    0
    Comment actions Permalink

Please sign in to leave a comment.