QUERY Pivot Table -Google Sheets - Query Pivot, Group By, Month, Year Functions Tutorial - Part 6
Vložit
- čas přidán 29. 08. 2024
- 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.chicagocom...
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 content. Thx for sharing this. Learning a lot!
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.
I love you master................... you r awesome
This is spectacular! It works!
Great step by step explanation!
Fantastic Great job!
10:40 select by year. Very useful tip.
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
min 05:43 the difference between PIVOT (by columns) and GROUP BY (by rows)... Muchas gracias!
Hi there - loving all your lessons they great. Can you please share the sample sheets you using?
Greetings from Brazil
Thank a lot for you tutorials!
another great video, thanx man
Awsome❤
Wonderful
perfectly well explained. thank you
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.
thank you, wonderful work
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.
Thanks for the video! is there a way to display the months as name of month instead of numbers?
Thanks a lot! That's really usful :).
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.
How do I convert the MONTH numbers to actual dates?
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 czcams.com/video/eQKmAcdVccs/video.html
What about the no_format options clause in google sheets
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?
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?
Is possible to create a column with the row total, like in pivot table?
Everytime I use this, a blank row or colum gets created. Any idea why
Can you please make a video on query progressive column
We need to pay you for those nice lessons
Hi.... Is possible to use a SELECT statement inside SUM in a QUERY function?
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
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?
Is there any way to show the values of a pivot but hide the pivot column headers?
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
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
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.
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 - can you please share the data set as well?
How to get total sum below?
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
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 ?