Total spend per field per month

[PaulaCom]

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 🙂 

[danspav]

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