Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Reporting

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Community
- :
- Splunk Answers
- :
- Using Splunk
- :
- Reporting
- :
- Re: How to identify fiscal year in dashboard repor...

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark Topic
- Subscribe to Topic
- Mute Topic
- Printer Friendly Page

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

organus

Explorer

11-15-2019
12:07 PM

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

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

to4kawa

Ultra Champion

11-15-2019
09:47 PM

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

.

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

to4kawa

Ultra Champion

11-15-2019
09:47 PM

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

.

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

organus

Explorer

11-18-2019
08:34 AM

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.

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

to4kawa

Ultra Champion

11-18-2019
08:37 AM

Thank you for the detailed explanation.

Happy splunking.

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

organus

Explorer

11-15-2019
12:34 PM

Can anyone suggest improvements on this?

Get Updates on the Splunk Community!

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

As security threats and their complexities surge, security analysts deal with increased challenges and ...