yogi_Query Data Pivot By Year And Month Based On Timestamp Column

Google Spreadsheet   Post  #2503

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Sep-01-2018

question by: Martin Konstantin Herrmann
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/xyRkN7MkGXw;context-place=mydiscussions
2 Years of seperate Data, pivot by Year and month
Hey Folks,

so here is the Situation.

I have a form where ppl enter data, basically members that join our club.

There are timestamps on when each member joined and who recruited them.

So far so good.

I managed to get a pivot working to show me the simple count on each timestamp:

=QUERY('Rekr Rohdaten'!A:N; "select J, COUNT(J) where J is not null and not(J) contains 'Gründung' group by J pivot month(A)+1"; 1)

However the problem here is month(A)+1 does not care which year it is.

I'd like to pivot for yyyy-mm instead of only mm or yyyy but i have been unable to get the exact thing going.

However i dont want to just cut the timestamps on the source table since i need the dd as well for other queries.

=QUERY('Rekr Rohdaten'!A:N; "select * where J is not null and not(J) contains 'Gründung' format A'YYYY-MM', C'YYYY-MM'"; 1)

and then use

=QUERY('t5'!A:N; "select J, COUNT(J) where J is not null and not(J) contains 'Gründung' group by J pivot A"; 1)

which basically pulls out the date from the first query (since i haven't figured out how to nest these queries) it still somehow knows the format and adds back the days.

i tried pivot dateformat(A;'yyyy-mm';1) but this seems not to work either as it requires a day.

Anyone knows a solution to pivot a timestamp in the format of yyyy-mm so the years are split?

Thanks (: