topic Re: Financial Year Revenue Comparison (Month / Year) in Splunk Dev

Financial Year Revenue Comparison (Month / Year)

venkata — Mon, 28 Jun 2021 11:33:10 GMT

can any one help to write a Query for below scenario?

Financial Year Revenue Comparison (Month / Year):

Compare Revenue with current Financial Year of the month

Compare Revenue with current-1 Financial Year of the month

Compare Revenue with current-2 Financial Year of the month

Current Financial Year Revenue Comparison

Current-1 Financial Year Revenue Comparison

Current-2 Financial Year Revenue Comparison

*Revenue – Sum of Total amount

X-axis = current-2,current-1,current

Y-axis = amount

Re: Financial Year Revenue Comparison (Month / Year)

ITWhisperer — Mon, 28 Jun 2021 12:48:37 GMT

How about something like this

Re: Financial Year Revenue Comparison (Month / Year)

venkata — Tue, 29 Jun 2021 06:22:12 GMT

Actual Result i got for query you have sharedExcepted Result

Thanks !! for the Query.

Please find the above attached actual result and excepted results SS and do needful.

Re: Financial Year Revenue Comparison (Month / Year)

ITWhisperer — Tue, 29 Jun 2021 07:16:11 GMT

Try it this way

Re: Financial Year Revenue Comparison (Month / Year)

venkata — Tue, 29 Jun 2021 13:17:46 GMT

Thank you !!!!!

Really helps a lot.....Query gives us a 90% solution

Can you Please explain a logic behind that "8" below?

| eval finmon=(8+finmon)%12
| eval previousyear=if(finmon>8,-1,0)

Re: Financial Year Revenue Comparison (Month / Year)

ITWhisperer — Tue, 29 Jun 2021 13:25:14 GMT

The 8 shift the number of the month so that April is 0 (4+8 mod 12), which mean Jan-Mar will be 9-11 respectively - these months belong to the financial year beginning in the previous calendar year, so -1 is added to the year to get the financial year for these months.

Re: Financial Year Revenue Comparison (Month / Year)

ITWhisperer — Tue, 29 Jun 2021 13:26:34 GMT

If your financial year begins in a different month to April, you will have to adjust the 8 accordingly

Re: Financial Year Revenue Comparison (Month / Year)

venkata — Tue, 29 Jun 2021 14:14:36 GMT

Great Got it!!!!!!!!!

Thank you so much.

Re: Financial Year Revenue Comparison (Month / Year)

venkata — Wed, 30 Jun 2021 11:34:02 GMT

Hi, Is that possible to plot like Current, current-1 and current-2 instead of 2021,2020 and 2019 on x-axis?????

Because we are comparing both Yearly and monthly data over graph right??

Re: Financial Year Revenue Comparison (Month / Year)

ITWhisperer — Wed, 30 Jun 2021 12:01:29 GMT

I removed the adjustment for shifting the month since you only really need to recognise if the month is before April (<4) to know it is in the previous financial year. Now replacing year with current, current-1 etc

Re: Financial Year Revenue Comparison (Month / Year)

venkata — Wed, 30 Jun 2021 12:08:05 GMT

Thank You !!! it's working .

Re: Financial Year Revenue Comparison (Month / Year)

venkata — Thu, 01 Jul 2021 06:08:01 GMT

if the financial year is starts from july 1st only change in the code is | eval previousyear=if(finmon<7,-1,0)

Correct??

Re: Financial Year Revenue Comparison (Month / Year)

venkata — Thu, 01 Jul 2021 06:26:39 GMT

Also only Current-2 data is displaying now in visualization

Re: Financial Year Revenue Comparison (Month / Year)

ITWhisperer — Thu, 01 Jul 2021 06:41:51 GMT

Correct

Re: Financial Year Revenue Comparison (Month / Year)

ITWhisperer — Thu, 01 Jul 2021 06:43:05 GMT

Not sure why that would be - can you post your current search (use a code block </> for clarity)?

Re: Financial Year Revenue Comparison (Month / Year)

venkata — Thu, 01 Jul 2021 06:50:14 GMT

Same code yesterday i got results for current, current-1,current-2 but now it displaying only current-2 .......today change in the month happend i mean today is july 1st is that the case??

Re: Financial Year Revenue Comparison (Month / Year)

ITWhisperer — Thu, 01 Jul 2021 07:04:44 GMT

Possibly, your index doesn't yet have any data for this July, which would account for no current but that doesn't explain current-1 not being there. Try removing the where clause and have a look at the data to see if there is any data for last July

Re: Financial Year Revenue Comparison (Month / Year)

venkata — Thu, 01 Jul 2021 07:14:32 GMT

Above is the output after where clause is removed.

Re: Financial Year Revenue Comparison (Month / Year)

ITWhisperer — Thu, 01 Jul 2021 07:19:19 GMT

So it looks like you don't have data for all the months in the previous years. Either remove the table command or add the finmon field to the list to see which months are covered (still without the where clause)

Re: Financial Year Revenue Comparison (Month / Year)

venkata — Thu, 01 Jul 2021 07:32:15 GMT

Yes, you're correct it doesn't have data for all the months in the previous years... this index i created with some random dummy data for testing purpose i guess in original data it might be their is a data presence for each and every month for pervious years as well.