September 7, 2021

# How to Calculate a Dynamic Selected and Prior Fiscal Year Tooltip in Tableau

By Joe McFarren

I recently encountered a problem using a string value of the fiscal year and how to show both a selected year and the prior year based on that selection. I couldnâ€™t just add the fiscal year filter to the filter pane and create a calculation to select the filtered year minus one, and I couldnâ€™t use date diff because fiscal year is a string value (in the format of FY19, FY20). An alternative is using a parameter as well as the field itself.

Using a parameter in this situation allows for two aggregation levels: current year and prior year. We can use the parameter selection to select the desired year and leave the [Fiscal Year] field for the prior year.

#### Create a String Parameter

The first step is to create a string parameter using the values from the fiscal year and assigning labels to show the same value (i.e. â€˜FY20â€™, â€˜FY19â€™).

We then set [Fiscal Year] equal to [Fiscal Year Parameter] and title this â€˜Fiscal Year Selection.â€™ This will allow us to simply set our selected year using the parameter instead of putting [Fiscal Year] on the filter pane:

[Fiscal Year Parameter] = [Fiscal Year]

#### Use the RIGHT Function to Calculate the Prior Year Selection

Since the fiscal year field is a string, we canâ€™t just use a date diff calculation to subtract one, so we have to use the RIGHT function to select the right two characters of the value (â€˜20â€™, in this case), use INT so we can subtract 1, turn it back to a string using STR and then append it to the string â€˜FY.â€™ In this case, we would get â€˜FYâ€™ + â€˜19â€™. We are now setting this calculation to â€˜FY19â€™ and the fiscal year parameter to â€˜FY20â€™. This calculation is our [Prior Year Selection]:

“FY” + STR(INT(RIGHT([Fiscal Year Parameter], 2)) – 1) = [Fiscal Year]

This creates a boolean calculation that essentially says, â€˜True or false? If I select FY20 using my parameter, which value minus one is my prior year?â€™. From the screenshot below, we can see that when we select FY20 using the Fiscal Year Parameter selector, the prior year from FY20 is indeed FY19:

#### Calculate Selected Year Sales

We now have a value for the current fiscal year (parameter) and prior fiscal year (calculated field). To get [Selected Year Sales], we use:

SUM(IF [Fiscal Year Selection] THEN [Sales] END)

Â

Similarly, to get [Prior Year Sales], we use:

SUM(IF [Prior Year Selection] THEN [Sales] END)

Â

We also need to create a prior year label for our tooltip, which uses the same string/integer/RIGHT function seen above:

“FY” + STR(INT(RIGHT([Fiscal Year Parameter],2)) – 1)

Â

Now that we have all of our fields letâ€™s show everything at once. We can see that when â€˜FY20â€™ is selected in the parameter, we have [Selected Year Sales] showing for the â€˜FY20â€™ row, we have [Prior Year Sales] showing for the â€˜FY19â€™ row, and a True value for the â€˜FY19â€™ row for the prior year, showing our prior year calculation is working correctly:

#### Create a Map View

To create a map view, we can add [State] to the viz and add [Selected Year Sales] to the size card. This shows sales by the state for our selected year. To include the prior year in the tooltip, we can format the tooltip:

And thatâ€™s it! This walkthrough is helpful if you have a non-date year field in your data set. In our case, [Fiscal Year] was a string value. You can then use a parameter as well as the field itself to show both current and prior year, and then add your metrics to your formatted tooltip to show everything appropriately. Hereâ€™s our final viz with tooltip. Since Iâ€™m based in Minnesota, letâ€™s look at Minnesotaâ€™s sales:

## More to explore

AI & ML

Data Strategy

Coalesce

### Coalesce Basics: Getting Started & Creating Your First Data Pipeline

Data Coach is our premium analytics training program with one-on-one coaching from renowned experts.