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:

Years:

Load * inline

[

Year

2010

2011

2012

2013

2014

2015

2016

2017

2018

2019

2020

];

**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:

*=**SubField(GetFieldSelections([Year],',',GetSelectedCount([Year])),',',1)*

Explanation:

The formula **GetFieldSelections()** results in a string with the selected values of a field.

**Field**

The field within a filter pane, in this example field [Year]

**Value_sep**

The seperator to use between the selected values, in this example a comma (',')

**Max_values**

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.

**Text expression**

The text expression for this example is a string with all selected values: *GetFieldSelections([Year],',',GetSelectedCount([Year]))*

**Delimiter**

Use the same delimiter as the value_seperator, in this example use comma (',')

**Field_no**

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:

*=SubField(GetFieldSelections([Year],',',GetSelectedCount([Year])),',',GetSelectedCount([Year]))*

Explanation:

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')*

Explanation:

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...

## Comments