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:
