I was wondering if it was possible to determine / calculate the minimum and maximum value of a selected field (in a filter pane) and with a few hours of investigation I succeeded.
1. Add a new app
For this example I used a new app and added the following data load:
Load * inline
2. Load the data and insert a filter pane
Next step is to load the data and insert a new filter pane. Add field Year as a filter dimension:
3. Insert a table and add a formula on dimension for the minimum selected year
First insert a table and add the formula:
The formula GetFieldSelections() results in a string with the selected values of a field.
The field within a filter pane, in this example field [Year]
The seperator to use between the selected values, in this example a comma (',')
The maximum number of values that may be added in the string. To add all the selected values to the string, you can use GetSelectedCount() formula. This is the number of selected values of a field. If you select one year, the result of the formula GetSelectedCount([Year]) is 1, if you select two years, the result will be 2, if you select all years, the result will be 10.
The next formula that is needed to determine the minimum value of all the selected values is SubField(). This is a formula to get one value in a string of values seperated by a specific seperator.
The text expression for this example is a string with all selected values: GetFieldSelections([Year],',',GetSelectedCount([Year]))
Use the same delimiter as the value_seperator, in this example use comma (',')
To retrieve the first selected value, use number 1.
4. Add a formula on dimension for the maximum selected year
Add the following formula to determine the maximum value of the selected year:
The same as the formula for minimum value, but instead of number 1, you have to retrieve the maximum number and that is: GetSelectedCount([Year])
5. Add a formula to determine if current year is in the selection
The previous formula can be combined with the formula SubStringCount() and then you can determine if the current year is part of the selection:
=If(SubStringCount(GetFieldSelections([Year],',',GetSelectedCount([Year])),Text(Year(Today()))), 'Yes', 'No')
With the formula SubStringCount(Text expression, sub_string) you can search how many times the sub_string occurs in the Text expression.
In this example: how many times occurs Text(Year(Today())) in GetFieldSelections([Year],',',GetSelectedCount([Year])).
In 'normal' language: how many times occurs Current year (converted to text) in String with all the selected values of field Year.
I hope this make sense and you can use this...