Index

  1. Basics
    • Setting a filter to the data in any expression
  2. Filters
    • Restricting a value displayed in filters
    • Filter between 2 dates

1 – Basics

With set analysis you can filter the data that will appear in a visual

eg. Sum({<[field to apply a filter]={‘filter’}>}[field to add up])

3 step easy build;

  • Set what you want to for eg add up

=Sum([a number])

  • After the first bracket add {<>} 

eg =Sum({<>}[a number])

  • Add the field and filter criteria you want to filter on between the <>

eg =Sum({<[a name]={‘bob’}>}[a number])

For the filter you are applying us ‘ ‘s to be explicit / literal i.e. to match something.

Use ” ‘s when the filter needs  ‘searching or evaluating’

eg =Sum({<[a name]={“*ob”}>}[a number])

If it’s a calculation that needs evaluating then add a ‘$’ sign

eg =Sum({<[Date]={“<=$(-Max(Date))”}>}[a number])

2.1 – Filter – restrict the values in a filter

You can restrict the values a user has available to filter the data by.  This makes that choice easier for them and hence makes training also easier / makes the sheet more intuitive.

The expression to use is an if / or statement. The example below shows only ‘Red, Black and Green pen’ in the filter pane.  These values all exist in the field ‘Pen Description’

eg

=if
(
[Pen Description]=’Red pen’ OR
[Pen Description]=’Black Pen’ OR
[Pen Description]=’Green Pen’,
[Pen Description]
)

2.2 – Filter – filter values between 2 dates

If you want to add up a value but only between 2 dates you may want to try something like this.

In the example below I have used variables vYearStart and vYearEnd, but you could just have easily used the exact date eg ’31/03/2019′ etc

eg

Sum({<field name where your date is={“>=$(=date(vYearStart)) <=$(=date(vYearEnd))”}>}field name that you want to add up)

want to filter on less than today…here you go;

DATE={“<=$(=Date(Today(), ‘DD/MM/YYYY’))”}