Unable to change bar chart to ONLY show the number of events for just the previous business quarter

[POR160893]

Hi,

I currently has a barchart like this which shows the number of requests per business quarter:

Here is the respective query:
index=servicenow sourcetype="snow:sc_task" dv_assignment_group="SECURITY-NETWORK-L3" description="Request for Dell firewall changes."
| stats latest(*) as * by dv_parent
| eval _time = strptime(dv_sys_updated_on, "%Y-%m-%d")
| eval Quarter=strftime(_time,"%Y" . "Q" . ceil((tonumber(strftime(_time,"%m"))+1)/4))
| stats count by Quarter

I need to alter this query to ONLY show the previous quarter, i.e. FY23Q4, After 1 week from today, the next quarter will start, so the bar chart will change to ONLY FY24Q1. Can you please me with this updated query?


Many thanks,

[yuanliu]

Not clear why you cannot simply exclude stats from current quarter.  This way, you only have previous quarter in stats.  Like this

index=servicenow sourcetype="snow:sc_task" dv_assignment_group="SECURITY-NETWORK-L3" description="Request for Dell firewall changes."
| stats latest(*) as * by dv_parent
| eval _time = strptime(dv_sys_updated_on, "%Y-%m-%d")
| eval Quarter=strftime(_time,"%Y" . "Q" . ceil((tonumber(strftime(_time,"%m"))+1)/4))
| where Quarter != strftime(now(),"%Y" . "Q" . ceil((tonumber(strftime(now(),"%m"))+1)/4))
| stats count by Quarter

[POR160893]

So, the financial year my employer is in is FY24, i,e, 2024. This began at the start of March as that was end of the previous financial year. So, right now, we are in FY24Q1. That will become FY24Q2 at the start of June and FY24Q3 at the start of September and FY23Q4 at the start of December.

The bar chart in question needs to show the number of FCR's for the PREVIOUS quarter ONLY.

Does anyone know how to incorporate these conditions into a drilldown?

[woodcock]

Like this:

index=servicenow sourcetype="snow:sc_task" dv_assignment_group="SECURITY-NETWORK-L3" description="Request for Dell firewall changes."
| eval _time = strptime(dv_sys_updated_on, "%Y-%m-%d")
| bin _time span=1q
| where _time = relative_time(now(), "@q-1q")
| eval Quarter=strftime(_time,"%Y" . "Q" . ceil((tonumber(strftime(_time,"%m"))+1)/4))
| stats count by Quarter

[gcusello]

Hi @POR160893,

add to your main search

index=servicenow sourcetype="snow:sc_task" dv_assignment_group="SECURITY-NETWORK-L3" description="Request for Dell firewall changes." earliest=-3mon@mon latest=@mon
| stats latest(*) as * by dv_parent
| eval _time = strptime(dv_sys_updated_on, "%Y-%m-%d")
| eval Quarter=strftime(_time,"%Y" . "Q" . ceil((tonumber(strftime(_time,"%m"))+1)/4))
| stats count by Quarter

Ciao.

Giuseppe

even if you don't nell more the division by quarter but you can only use stats count.

[POR160893]

Hi,

You query is giving the following:

It should be given FY23Q4 as that was the previous quarter for out financial year. We are CURRENTLY in FY24Q1 but we are interested in just the previous quarter.

Can you please help?

[gcusello]

Hi @POR160893,

sorry but i don't understand: we ae in april, so the 2023Q2, so the previous quarter is 2023Q1, what's the problem?

There could be a problme e.g. in May because using my time frame it takes the three previous months that I suppose isn't your need, so please try this:

index=servicenow sourcetype="snow:sc_task" dv_assignment_group="SECURITY-NETWORK-L3" description="Request for Dell firewall changes." earliest=-6mon@mon latest=@mon
| stats latest(*) as * by dv_parent
| eval _time = strptime(dv_sys_updated_on, "%Y-%m-%d")
| eval Quarter=strftime(_time,"%Y" . "Q" . ceil((tonumber(strftime(_time,"%m"))+1)/4))
| stats count by Quarter

Ciao.

Giuseppe

[POR160893]

So, the financial year my employer is in is FY24, i,e, 2024. This began at the start of March as that was end of the previous financial year. So, right now, we are in FY24Q1. That will become FY24Q2 at the start of June and FY24Q3 at the start of September and FY23Q4 at the start of December.

The bar chart in question needs to show the number of FCR's for the PREVIOUS quarter ONLY.

Does that answer your question?