QUERY Pivot Table -Google Sheets - Query Pivot, Group By, Month, Year Functions Tutorial - Part 6
HTML-код
- Опубликовано: 27 окт 2017
- Video tutorial series about QUERY function in Google Sheets.
In this tutorial we'll cover how to create pivot tables with QUERY function in Google Sheets, how to use month and year functions and also how to create a calculated field using Query function.
Website:
www.chicagocomputerclasses.com/
Please keep these coming! Every time I watch one of your videos, I learn something that I didn't know was possible with Google Sheets. Thanks for graciously sharing with the rest of us.
You are my preferred 'teacher'. Thanks a lot for everything! I always learn with your lessons! I love them, actually.
Great step by step explanation!
10:40 select by year. Very useful tip.
Thank a lot for you tutorials!
Great content. Thx for sharing this. Learning a lot!
another great video, thanx man
Thanks a lot! That's really usful :).
thank you, wonderful work
This is spectacular! It works!
Having watched this, I now understand pivot tables much better!
I don't understand how I managed without the Query function for so long!
Cool.
perfectly well explained. thank you
Hi there - loving all your lessons they great. Can you please share the sample sheets you using?
Fantastic Great job!
This is awesome! Is there a way to sort the Pivoted columns? Like having the result in this arrangement - > Western, Southern, Northeastern, Midwestern and not vice versa.
I love you master................... you r awesome
Greetings from Brazil
min 05:43 the difference between PIVOT (by columns) and GROUP BY (by rows)... Muchas gracias!
Thanks for the video! is there a way to display the months as name of month instead of numbers?
We need to pay you for those nice lessons
I've found the way how to replace numbers of the months with names:
(month(A)+1)*30 , then format the column that contains months to display months' names
Google Sheets pivot tables have a 'Pivot date group' function (available in a context menu) that lets me group a column of individual dates by Year-Month, i.e. Jan 2019, Feb 2019, March 2019, etc. Can I do this with a Query?
Awsome❤
Wonderful
Good day!, i was wondering if you came across a pivot then somehow it gives an extra column with 0 value label even if my where only limit on the selected week
example, = query(data), select (A)Name, count(A)Name where C = 'WB 02/27/2023' pivot (B) days. it creates a pivot of days within a week and somehow it appears 0 on the last column with value. and sometime if it repeats a column like monday, tuesday and wednesday and then another monday and tuesday again.
🙏🏻
Hi.... Is possible to use a SELECT statement inside SUM in a QUERY function?
Is there any way to show the values of a pivot but hide the pivot column headers?
Had a quick question on Query ....I have data in which one column has number format as " AB12234" when I use query function it's gives put as " " can you please provide resolution for this .... thank you
Hi, is it possible to group by Year and Month without creating additional columns? Like "2020-05 - value, 2020-06 - value..." - all written in a rows?
I have the same question. Anyone?
Please, can we use sum and pivot twice or more in the query formula? For example select and group by sales rep in the rows and then sum sales AND sum COGS in colunms?
You can nest one QUERY inside of another QUERY. I have a video covering nested queries in these series, that should help you see how.
Is possible to create a column with the row total, like in pivot table?
Hi, good video!. Can I sort columns created by pivot sentence in this query, in descent order. (ex.: 2017, 2016, 2015)
docs.google.com/spreadsheets/d/1fL4FuJzcIENFLNR01Ptywbre1Bpy7e5KqlD1XYDXWbY/edit?usp=sharing
Hi, were you able to find a solution to your request?
Everytime I use this, a blank row or colum gets created. Any idea why
Excelent video. But what about to rename the column 'month(date)' in the pivot table? I know it is done with 'LABEL' but It doesn't work with this more complex table. Thank you
label month(date) 'Month'
Hi! Thank you so much for your answer. But I had some problems.
In the minute 13:00 Column 'A' have a long header. So do I have to copy exactily that text and apply the label? Where do I write the label? before the first comma, after group by or after the pivot?
Thanks again.
Just repeat whatever you have.Check out this video, it should make it clear ruclips.net/video/eQKmAcdVccs/видео.html
Hi there - can you please share the data set as well?
Hey, how to convert the query month to the formula "mmmm yyyy"? I cant seem to do it by TEXT(MONTH(A)),"mmmm yyyy" in the query
Sorry without the yyyy
Can you please make a video on query progressive column
Any idea on how to add Grand total row and column, the same as the regular PivotTables have? I need a dynamic solution, opened to suggestions.
I suppose that depends on your pivot layout, but you should be able to add the totals as an array.
@@ExcelGoogleSheets Can you please explain in upcoming video. thanks
How can you create a calculated field in a query for TIME values? I'm trying to subtract finish time and start time to get a 'TIME SPENT' value in my query. Thanks.
Depends on how your times are entered, but generally that would be (TimeEndCol-TimeStratCol)*24
@@ExcelGoogleSheets It says "Can't perform the function difference on values that are not numbers." Ideas? Thank you.
Seems like your times are not really times. Maybe it's just text that looks like time?
Hi there, thanks fot the video,
I'm trying to switch "pivot by month(A)" to a weekly breakdown but it do not work;
I've tried "pivot by week(A)" or "pivot by weeknum(A)"
What am i doing wrong here ?
week and weeknum functions don't exist in QUERY language
@@ExcelGoogleSheets thanks for the input,
I've actually reverse to a traditionnal sumifs.
i wanted to have a table
Dimension : my metrics like clicks, costs...
split by : day, weeks or month based on a dynamic parameter in the sumifs argument.
I've tried the indiret fonction but i couldn't make it work.
Any clue here.
What about the no_format options clause in google sheets
How do I convert the MONTH numbers to actual dates?
How to get total sum below?
thank for videos.. I need query inculuding show data between two date.. Im still not understand..
You will need to use two operators > and < or >= and = date '2018-02-01' and B
Can I have your file ?
how to use alias in this query?
What kind of alias?
@@ExcelGoogleSheets if I will write G-H, It should display Gross Profit as a header of column