Reporting

How to identify fiscal year in dashboard reporting

organus
Explorer

How to find the fiscal year

Finding a calendar year in splunk is straightforward using the built-in date functions. But sometimes it is important to classify data based on a fiscal year. For instance, the federal fiscal year starts October 1st and ends September 30th. So what is a poor analyst to do?

Floor it!

Use the floor function to do some math and always get the right number. Let's take a look at an example search.

eval FiscalYear=tonumber(theYear) + floor(tonumber(theMonth) / 10) | eval FiscalYear= "FY" + substr(FiscalYear,3,2)

For simplicity, let's start with a date of "2019-11-01" and ignore the hours, minutes, etc. This date represents November 01, 2019. Since it is after October 1st, it is within the Fiscal year of 2020, or FY20.

We'll extract the 4-digit year and the month as a 2-digit number using strftime, "%Y", and "%M". For details, see https://docs.splunk.com/Documentation/SplunkInvestigate/Current/SearchReference/DateandTimeFunctions. This is well-documented in other threads, so we will act as though you've got this piece figured out.

So now, let's say that you parsed your date of interest into the year, as a string named theYear, and the month, as a string named theMonth.

Next, it's important to observe one more thing: because we are finding a fiscal year that starts in October, we need to pay attention to conditions where the months are October, November and December. These months have respective numbers of 10, 11, and 12. They are greater than or equal to 10. And this means that we can resort to integer math! Yeah!

Let's review the formula again. Here it is:

eval FiscalYear=tonumber(theYear) + floor(tonumber(theMonth) / 10) | eval FiscalYear= "FY" + substr(FiscalYear,3,2)

The first eval function has two summed terms, tonumber(theYear) and floor(tonumber(theMonth) / 10).

The first term is straightforward. It converts the theYear value from a string to a number, and the result, in this case, is 2019.

The second term is where the magic happens. Evaluating from inside to outside, we start by converting the month to a number and then dividing by 10. Remember, 10 is the numeric value of October. In this case, we end up with an intermediate value of 11/10, or 1.1. But then we take that value and we apply the floor function to it. This function simply returns a value by ignoring decimal remainders so our result for this term changes from 1.1 to 1.

Then we add the two results together to get FiscalYear = 2019 + 1, or FiscalYear = 2020. That's the right number!

The second eval method converts the 4-digit fiscal year into a 4-letter acronym for presentation purposes. It suffers from the Y2K problem, but for most reporting—reporting that isn't interested in the twentieth century—it is sufficient.

It takes the newly calculated fiscal year, selects the last two digits, and concatenates it with the letters "FY".

eval FiscalYear= "FY" + substr(FiscalYear,3,2)

So that 2020 becomes "FY20".

One last thought: we divided by 10 because we were interested in the federal fiscal year, which starts on October 1st, and 10 is the numeric value for October. But we could have just as easily divided by 7 to start a different fiscal year with July or chosen any month to make a custom fiscal year of our own.

1 Solution

to4kawa
Ultra Champion
| makeresults count=2
| streamstats count
| eval _time = if (count==2,relative_time(_time,"-20y@month"), _time)
| makecontinuous span=1month _time
 `comment("this is sample data")`
| eval FY = if(tonumber(strftime(_time,"%m")) >=10,"FY".strftime(relative_time(_time,"+1y"),"%y"),"FY".strftime(_time,"%y"))

Hi,I tried to make FY from _time.
After that, please choose where or search.

View solution in original post

to4kawa
Ultra Champion
| makeresults count=2
| streamstats count
| eval _time = if (count==2,relative_time(_time,"-20y@month"), _time)
| makecontinuous span=1month _time
 `comment("this is sample data")`
| eval FY = if(tonumber(strftime(_time,"%m")) >=10,"FY".strftime(relative_time(_time,"+1y"),"%y"),"FY".strftime(_time,"%y"))

Hi,I tried to make FY from _time.
After that, please choose where or search.

organus
Explorer

Oh, this is a very nice answer!

| makeresults count=2
| streamstats count

The first two lines create test events.

| eval _time = if (count==2,relative_time(_time,"-20y@month"), _time)

The next line pushes the second test event back into the past 20 years. This works if the example is run before the year 2020, but it is easy to adjust--just subtract more years if you are running it in the future.

| makecontinuous span=1month _time

The next line breaks everything into dates with the form YYYY-MM, so that you end up with hundreds of test values. Now the payload can be tested with the following line:

| eval FY = if(tonumber(strftime(_time,"%m")) >=10,"FY".strftime(relative_time(_time,"+1y"),"%y"),"FY".strftime(_time,"%y"))

And it works for twentieth century fiscal years! Very nice.

0 Karma

to4kawa
Ultra Champion

Thank you for the detailed explanation.
Happy splunking.

0 Karma

organus
Explorer

Can anyone suggest improvements on this?

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...