Financial Year Revenue Comparison (Month / Year)

[venkata]

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 Vs Compare Revenue with current-1 Financial Year of the month Vs Compare Revenue with current-2 Financial Year of the month

Current Financial Year Revenue Comparison Vs Current-1 Financial Year Revenue Comparison Vs Current-2 Financial Year Revenue Comparison

*Revenue – Sum of Total  amount

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

Y-axis = amount

[ITWhisperer]

How about something like this

| bin _time span=1mon 
| stats sum(amount) as thismonth by _time
| eval finmon=strftime(_time,"%m")
| eval finmon=(8+finmon)%12
| eval previousyear=if(finmon>8,-1,0)
| eval finyear=strftime(_time,"%Y")
| eval finyear=previousyear+finyear
| eventstats sum(thismonth) as financialyear by finyear
| eval currentmonth=strftime(now(),"%B")
| eval currentmon=strftime(now(),"%m")
| eval currentmon=(8+currentmon)%12
| where finmon=currentmon
| table thismonth financialyear finyear
| transpose 0 header_field=finyear

[venkata]

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. 

[ITWhisperer · Accepted solution]

Try it this way

| bin _time span=1mon 
| stats sum(amount) as thismonth by _time
| eval finmon=strftime(_time,"%m")
| eval finmon=(8+finmon)%12
| eval previousyear=if(finmon>8,-1,0)
| eval finyear=strftime(_time,"%Y")
| eval finyear=previousyear+finyear
| eventstats sum(thismonth) as financialyear by finyear
| eval currentmonth=strftime(now(),"%B")
| eval currentmon=strftime(now(),"%m")
| eval currentmon=(8+currentmon)%12
| where finmon=currentmon
| table finyear thismonth financialyear

[venkata]

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)

[ITWhisperer]

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

[ITWhisperer]

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.

[venkata]

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

[ITWhisperer]

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

| bin _time span=1mon 
| stats sum(amount) as thismonth by _time
| eval finmon=strftime(_time,"%m")
| eval previousyear=if(finmon<4,-1,0)
| eval finyear=strftime(_time,"%Y")
| eval finyear=previousyear+finyear
| eventstats sum(thismonth) as financialyear by finyear
| eval currentmonth=strftime(now(),"%B")
| eval currentmon=strftime(now(),"%m")
| eval currentyear=strftime(now(),"%Y")
| eval finyear=if(finyear=currentyear,"current","current-".(currentyear-finyear))
| where finmon=currentmon
| table finyear thismonth financialyear

[venkata]

Also only Current-2 data is displaying now in visualization

[ITWhisperer]

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

[venkata]

index="home2"
| bin _time span=1mon
| stats sum(AMOUNT) as thismonth by _time
| eval finmon=strftime(_time,"%m")
| eval previousyear=if(finmon<4,-1,0)
| eval finyear=strftime(_time,"%Y")
| eval finyear=previousyear+finyear
| eventstats sum(thismonth) as financialyear by finyear
| eval currentmonth=strftime(now(),"%B")
| eval currentmon=strftime(now(),"%m")
| eval currentyear=strftime(now(),"%Y")
| eval finyear=if(finyear=currentyear,"current","current-".(currentyear-finyear))
| where finmon=currentmon
| table finyear thismonth financialyear

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

[ITWhisperer]

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

[venkata]

Above is the output after where clause is removed.

[ITWhisperer]

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)

[venkata]

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. 

[venkata]

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

Correct??

[ITWhisperer]

Correct

[venkata]

Thank You !!! it's  working  .

[venkata]

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

Thank you so much.