Total spend per field per month

PaulaCom — Wed, 20 Sep 2023 12:03:09 GMT

Hi All

i am struggling with a query and appreciate some help please

i received the data on csv file - timestamp is today

i'm interested in 3 fields Account_No , Total and Order_Date

My view looks like this:

Account_No Total Order_Date

123 15.00 1/01/2023

123 35.00 15/02/2023

123 45.00 19/02/2023

456 15.00 1/01/2023

456 50.00 25/01/2023

456 10.00 19/02/2023

I'd like a view like this

Account_No Jan Feb

Total Sum 123 15.00 80.00

456 65.00 10.00

My main issue is using the eval to change the date format that appears in the csv file from 01/01/2023 to read January 2023 or even just January will probably do for this exercise.

I've come up with this so far

| eval Order_Date = replace(Order_Date,"01", "January") but firstly i see 06/January/2023 and not just January

my other issue is that if any other month has 01 in it (for example 01/07/2023 it appears like this January/07/2023)

Any ideas?

Also i started looking at stats list command to group all Account_Nos together

main search.....
| fields Order_Date Account_No Total
| stats list(Total) as Total by Account_No

i am unable to figure out how to get the subtotals for each Account_No

Any pointers would be appreciated

Thank you 🙂

Re: Total spend per field per month

danspav — Fri, 19 Jan 2024 04:56:10 GMT

Hi @PaulaCom,

Here's a way to convert your date to "Jan", "Feb" etc:
strftime(date_field, format) see docs

We can use the field Order_Date like this:
eval month = strftime(strptime(Order_Date, "%d/%m/%Y"), "%b")

That adds another step of converting the date to a unix timestamp, then converting that timestamp to the Month in english.

Now that we have the month, we can make it a field by using special curly brackets:

| eval {month} = Total

That will create a field called "Jan" or "Feb" with the value of the total for sales.

Here's the Search all together:

|makeresults | eval data="Account_No=\"123\", Total=\"15.00\", Order_Date=\"1/01/2023\"@@Account_No=\"123\", Total=\"35.00\", Order_Date=\"15/02/2023\"@@Account_No=\"123\", Total=\"45.00\", Order_Date=\"19/02/2023\"@@Account_No=\"456\", Total=\"15.00\", Order_Date=\"1/01/2023\"@@Account_No=\"456\", Total=\"50.00\", Order_Date=\"25/01/2023\"@@Account_No=\"456\", Total=\"10.00\", Order_Date=\"19/02/2023\"" | makemv data delim="@@" | mvexpand data | rename data as _raw | extract ``` The above just creates the test data``` | eval month = strftime(strptime(Order_Date, "%d/%m/%Y"), "%b") | stats sum(Total) as Total by Account_No, month | eval {month}=Total | fields - Total, month | stats sum(*) as * by Account_No | table Account_No, Ja*, Fe*,Ma*,Ap*,Ma*,Jun*, Jul*,Au*,Se*,Oc*,No*,De*

The last table bit at the end is so that the months are listed in the right order.

The result is:

Hopefully that gets you closer to what you were looking for.

Cheers,

Daniel

topic Re: Total spend per field per month in Splunk Search

Total spend per field per month

Re: Total spend per field per month