Trying to get month over month with detail.

[tmblue]

My current working and pretty one is this:

|eval Owner=ProductName | stats sum(Cost) as Total by TimePeriod, Owner | eval Total=round(Total, 2) |chart values(Total) over TimePeriod by Owner

The above works great for showing my exact spend broken out on product per day (I have that as week to date), but I'm running around in circles trying to figure out how to get the same data but by month.

Not quite what I want:

|eval Owner=ProductName | timechart span=1mon sum(Cost) as Monthly_Cost | eval Monthly_Cost=round(Monthly_Cost, 2)

So I would like the top one but per month, vs per day.. I am just struggling trying to figure it out. The detail of the first one is a real good visual on if something is going a bit nuts. And being able to see the same breakout per month, would allow to see trending not just by month but by ProductName.

thanks!

[Vijeta · Accepted solution]

You can use bin span.

|eval Owner= ProductName| bin span= 1mon TimePeriod| stats  sum(Cost) as Total by Owner TimePeriod| eval Total=Total(round,2)| chart values(Total) over TimePeriod by Owner 

[tmblue]

Thanks, but getting an error.

So I fixed the bin command I believe it needs to be used as bin _time span=1mon, when I fix that. It doesn't like my TimePeriod (which is a field (%F) in my data:

|eval Owner=ProductName| bin _time span=1mon TimePeriod| stats sum(Cost) as Total by Owner TimePeriod| eval Total=Total(round,2)| chart values(Total) over TimePeriod by Owner

Error in 'bin' command: Invalid argument: 'TimePeriod'

[Vijeta]

Just make it bin span=1mon _time and do the stats and chart using _time instead of TimePeriod.

[tmblue]

You know I started changing things over to that, but said "well _time has nothing to do with my TimePeriod, so I bailed.. but.. okay. So closer 🙂

If I keep it as is just replacing the TimePeriod with _time, it has a hard time with my total change to 2 decimal points and crys

|eval Owner=ProductName| bin span=1mon _time | stats sum(Cost) as Total by Owner _time | eval Total=Total(round,2)| chart values(Total) over _time by Owner

Error in 'eval' command: The 'total' function is unsupported or undefined.

Now I remove ; eval Total=Total(round,2) , which I really can't do, numbers are just too long with out it.

This looks right, other than the 2 decimals and the _time is including hours and offset,

|eval Owner=ProductName| bin span=1mon _time | stats sum(Cost) as Total by Owner _time | chart values(Total) over _time by Owner

Now i see time reference like 2018-10-01T00:00:00.000-7:00 That's a bit long, would like it just to be 2018-10-01 or (%F)

But it's closer than I've been able to get so far 🙂

[Vijeta]

It’s | eval Total= round(Total,2)

My bad I didn’t realize while typing .

[tmblue]

HAH a bit of work but I'm dancing, needed to add the eval_time to change the format and your correction on my 2 decimal point fixed that piece. So yes !!!!!

|eval Owner=ProductName| bin span=1mon _time | eval _time = strftime(_time,"%F") | stats sum(Cost) as Total by Owner _time | eval Total= round(Total,2)| chart values(Total) over _time by Owner

Thank you for working it through with me, def the right pointers!