Line graph using csv

[sandyjov1]

I am attempting to make a line graph with information from a csv w/ info from the past year.

	Nov 2020	December 2020	January 2021	February 2021
Events	19	9	5	7
Cleared	3	1	1	7
Incidents	3	1	1	0
False Positives	16	8	4	7

I need each category to have its own line on  a line graph. The Months would be on x axis (nov20-nov21). It doesn't seem complicated, but cant seem to get the results .  Any help would be appreciated. Thanks 

[ITWhisperer · Accepted solution]

You need to use transpose, then get rid of some added fields and sort the dates

| makeresults
| eval _raw="Category,November 2020,December 2020,January 2021,February 2021
Events,19,9,5,7
Cleared,3,1,1,7
Incidents,3,1,1,0
False Positives,16,8,4,7"
| multikv forceheader=1
| fields - linecount _raw _time
``` the lines above set up some sample data ```
| transpose 0 header_field=Category column_name=Date
| eval Date=strptime(Date."-01","%B_%Y-%d")
| where isnotnull(Date)
| sort 0 Date
| fieldformat Date=strftime(Date,"%B %Y")

[sandyjov1]

Thank you for help!
Is there a better way for me to setup my csv, so that I wouldn't need to transpose?

[ITWhisperer]

Put the dates in the first column and the categories across the top - it depends on how you set it up in the first place

[sandyjov1]

Thanks, this worked! Appreciate your efforts 🙂

[bowesmana]

Transpose the CSV, so the rows are months and the columns are categories. That is how the timechart command will work to produce data for a time based chart where date is on the X axis.

[sandyjov1]

Thanks! 
what would the timechart query look like if I fixed my csv that way?

[bowesmana]

If your CSV looks like this

and it's in a file called events.csv, then all you need is this query

| inputlookup events.csv
| eval Date=strptime(Date."-01","%B %Y-%d")
| sort 0 Date
| table Date *

You don't need to use the timechart as your data is already in the format a timechart would create

@ITWhisperer 's solution is perfectly good for your data as it stands, so unless it makes sense for you to change the data, just use his solution. If you are going to use the data in many places, just create a macro that would do all the initial load and transpose so your query just uses the macro to keep it clean. If you change your data format you just change the macro and everything will continue to work.

[sandyjov1]

Appreciate your help!