Google-sheets – Line chart with the number of tickets created by date

google sheets

Here's what my sheet looks like:

I'm trying to create a Line chart with 3 lines showing the number of tickets we get that labeled 1) billing, 2) info or 3) troubleshooting by date. So I want

the date column on the x
the number of times a date in the date column appears with the same label in the label1 column on the y

I tried making a pivot table and failed–it doesn't seem to be compatible with my date column.

I feel like the solution involves countifs but I can't wrap my head around what to do next.

Can anyone offer any insight or point me the right direction?

Best Answer

The query formula can do things like this. Example:

=query(A2:B, "select todate(A), count(B) where B = 'info' group by todate(A) order by todate(A) asc")

This selects the dates (ignoring times) from column A for which there is at least one entry 'info' in B. The number of such entries is also returned.

If you want to also have 0 entered for the dates when no qualifying tickets were created, that can be done by applying the method of Fill zeros for the dates when no data was recorded to the output of query.

Related Solutions

Google-sheets – Forcibly format column as Date in sheets onOpen() with Javascript

The method setNumberFormat does the job:

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range = sheet.getRange("C:C");
  range.setNumberFormat("yyyy/MM/dd");
}

This formats column C as yyyy/MM/dd. Many other date formats are available: the method setNumberFormat supports Java's SimpleDateFormat.

Google Sheets – Use COUNTIFS in Complex Formulas

You can do a count for each month by transforming an array formula to only count based on that month:

=COUNTIF(ARRAYFORMULA(TEXT('Canada Twitter Raw Data'!A2:A,"MM")),"01")

change the "01" for each month so february would look like this:

=COUNTIF(ARRAYFORMULA(TEXT('Canada Twitter Raw Data'!A2:A,"MM")),"02")

Best Answer

Related Solutions

Google-sheets – Forcibly format column as Date in sheets onOpen() with Javascript

Google Sheets – Use COUNTIFS in Complex Formulas

Related Topic