2

The data in the 'Input' sheet starts in cell A1 and looks like this:

2021 04 06 7 43
2021 04 06 11 15
2021 05 01 10 11
2021 05 01 13 18

These are datetime values so 2021 05 01 13 18 means 2021 May 1st at 1:18 pm.

How do I plot a line chart that shows the number of occurrences of a particular hour? I need the hours in the X axis, count of hours in the Y axis, and a separate line for each date. Further, the rows should be filtered by the start date in Output!B1 and end date in Output!B2.

1

Choose Insert > New sheet and enter this formula in cell A1:

=arrayformula( 
  query( 
    { 
      query( 
        if( 
          len(Input!A1:A), 
          { 
            text(datevalue(left(Input!A1:A, 10)), "yyyy-mm-dd"), 
            hour(timevalue(regexreplace(Input!A1:A, "(\d+) (\d+)$", "$1:$2"))) 
          }, 
          iferror(1/0) 
        ), 
        "select Col1, Col2, count(Col2) 
         where '" & text(Output!B1, "yyyy-mm-dd") & "' <= Col1 
           and Col1 <= '" & text(Output!B2, "yyyy-mm-dd") & "' 
         group by Col1, Col2 
         label count(Col2) '' ", 
        0 
      ); 
      { iferror(sequence(24)/0, "baseline"), sequence(24), iferror(sequence(24)/0, 0) } 
    }, 
    "select Col2, sum(Col3) 
     group by Col2 
     pivot Col1 
     label Col2 'Hour' ", 
    0 
  ) 
)

Then create a chart based on the result table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.