Index
- Basics
- Setting a filter to the data in any expression
- 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’))”}
