Dashboard - Spend Amount Over Time

[Declan123]

Hi All,

I am trying to create a scatter dashboard or similar in Dashboard Studio to show debit transaction amounts over time.

A query like this works well in Search, but translates poorly to the dashboard:

source="Transaction File.csv" "Debit Amount"="*" | stats values("Debit Amount") BY "Posted Transactions Date"

I am aware I likely need to convert the the date from string format to date format within my search, something to the effect of:  |eval Date = strptime("Posted Transactions Date","%d/%m/%y")

But I am struggling to get the final result.

I have also played around with using the _time field instead of Posted Transaction Date  field and with timecharts without success which I think is likely also a formatting issue. 

Eg: 

source=source="Transaction File.csv" | timechart values("Debit Amount")

As there are multiple debit amount values per day in some cases, I would ideally like a 2nd similar dashboard that sums these debits per day instead of showing them as individual values whilst also removing 1 outlier debit amount value of 7000.

Struggling a bit with the required search(s) to get my desired dashboard results.

Any help would be appreciated, thank you!

[Declan123]

Hi @PickleRick ,

Thank you for your reply.

I sorted the date format at the time of uploading the CSV as %d/%m/%Y. By doing this, all of _time events are not the same and are actually the date from the Posted Transactions Date field.

I figured it out in the end by realizing that a Scatter chart is not suitable for date values on the X axis so I changed it to a line chart, and used this search query:

index=main source="Transaction File.csv" | fields "Posted Transactions Date" _time "Debit Amount" "Spending Type" | timechart sum("Debit Amount")

Regarding your comment on quotes, I have noticed that comparing my searches to searches I have seen elsewhere. 

However I seem to need to use double quotation marks on fields. For example:

source="Transaction File.csv" 'Debit Amount'="*" (returns no events)

source="Transaction File.csv" Debit Amount="*" (returns no events)

However source="Transaction File.csv" "Debit Amount"="*" (returns all events)

[PickleRick]

Well, Splunk can be a bit inconsistent sometimes about using quotes. But when you're referencing something as an argument to the function (or a rvalue in an assignment), double quotes will mean that Splunk will use literal string.

So

| eval new_value="Posted Transaction Date"

Would yield a literal string, not the field contents. (Same with strptime arguments).

But yes, in other places that can be a bit unobvious which form to use at any given point.

[PickleRick]

For timechart to work you need to have reasonable _time field. I suppose that you ingested whole csv file at once and didn't properly parse the time from your data so your _time will be the same for all events, right?

The way around it would be to overwrite the _time field with the contents of your Posted Transaction Date

index=<yourindex> ...
| eval _time=strptime('Posted Transaction Date',"%proper%time%format")
| timechart values('Debit Amount')

Key thing here is specifying proper time format for the strptime command.

Also be cautious about using proper quotes.