0

I have a sheet with 2500 dates & times of when customers have placed their order.

I want to do a heatmap or a bar graph to show which days of the week are the busiest. I rounded the exact times using mround to round up or down to the nearest whole hour.

But when I pivot on the data, for example, The 'Sunday 10:00 AM' at the beginning of the column I guess is not the same as the 'Sunday 10:00 AM' for the following week, and so on.

I tried converting the date to_text. Which worked, but then the pivot table couldn't be sorted in chronological order.

Improve this question
6
  • can you share a copy of your sheet?
    – user0
    May 22, 2019 at 13:44
  • Here's a link to the copy of the sheet docs.google.com/spreadsheets/d/…
    – Matt Winer
    May 22, 2019 at 14:07
  • you want that heatmap/chart to represent one week interval to see the "heat" per weekdays, right?
    – user0
    May 22, 2019 at 14:21
  • also, is Sunday for you the 1st day of the week or 7th?
    – user0
    May 22, 2019 at 14:35
  • yeah so basically I want the total number of transactions for all the Sunday 10:00 AM summed up into one entry. Which, if I filter the column it works correctly (looks like there are 80 entries for this time). But the pivot table does not work.
    – Matt Winer
    May 22, 2019 at 14:35

1 Answer 1

Reset to default
0

instead of your formula in B column and the whole pivot table, use:

=ARRAY_CONSTRAIN(ARRAYFORMULA(REGEXREPLACE(QUERY(IF(LEN(A2:A), 
 {WEEKDAY(A2:A), TEXT(A2:A, "dddd am/pm hh:00 am/pm")}, ), 
 "select Col2,count(Col2),Col1 
  where Col2 is not null 
  group by Col2,Col1 
  order by Col1
  label count(Col2)''", 0)&"AM ", "PM |AM ", "")), 999^99, 2)

0

0

0

Improve this answer
5
  • That works great. and I don't mean to get picky. Is there a way to then sort it so the dates are chronological?
    – Matt Winer
    May 22, 2019 at 14:56
  • you mean AM/PM to be in chrono order?
    – user0
    May 22, 2019 at 14:58
  • yeah so when I graph it, everything is in order. Sunday 10am, 11am, 12pm,... Monday 8am, 9pm, etc
    – Matt Winer
    May 22, 2019 at 15:25
  • yeah that would work. for sure
    – Matt Winer
    May 22, 2019 at 16:16
  • @MattWiner answer updated
    – user0
    May 22, 2019 at 16:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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