financial Calendar

[DanielaEstera]

Hi, community members

I am trying to write a query that looks like this:

| dbxquery query="select VULNERABILITY_LIFECYCLE, SOURCE, CLOSURE_FY, CLOSURE_QUARTER, CLOSURE_DATE
from table [...]"
| eval MONTH=strftime(strptime(CLOSURE_DATE,"%Y-%m-%d %H:%M:%S"),"%m")
| eval SURSA = if(SOURCE!="QUALYS-P","Confirmed", "Potential")
| chart count over MONTH by SURSA

My problem is that I want this chart to represent a financial year, not a calendar year. How can I do this? (also,  without skipping months)

DanielaEstera_0-1632391869249.png

Thank you for your support,

Daniela

[ITWhisperer]

Include the year in the MONTH field? Add 8/subtract 4 (assuming April) and take a modulus 12 and add 1?

[DanielaEstera]

not quite sure. did you mean :

| eval MONTH=strftime(strptime(CLOSURE_DATE,"%Y-%m-%d %H:%M:%S"),"%m-%Y")
| eval luna = (MONTH + 4 )mod 12 + 1

??

[ITWhisperer]

The values will be sorted so year should come before month.

| eval MONTH=strftime(strptime(CLOSURE_DATE,"%Y-%m-%d %H:%M:%S"),"%Y-%m")

or change the month so the April is 1 and March is 12

| eval luna = ((MONTH + 8 ) % 12) + 1