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.
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.
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.
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
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?
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?
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
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.
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 ?
@@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.
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.
Hi there - loving all your lessons they great. Can you please share the sample sheets you using?
This is spectacular! It works!
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
Great step by step explanation!
Fantastic Great job!
min 05:43 the difference between PIVOT (by columns) and GROUP BY (by rows)... Muchas gracias!
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.
Hey, did you find out? I wanted to ask just that :)
10:40 select by year. Very useful tip.
Thanks for the video! is there a way to display the months as name of month instead of numbers?
Everytime I use this, a blank row or colum gets created. Any idea why
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.
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?
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?
Greetings from Brazil
Thank a lot for you tutorials!
another great video, thanx man
Hi.... Is possible to use a SELECT statement inside SUM in a QUERY function?
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
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
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?
Can you please make a video on query progressive column
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.
What about the no_format options clause in google sheets
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 th-cam.com/video/eQKmAcdVccs/w-d-xo.html
Awsome❤
How do I convert the MONTH numbers to actual dates?
Wonderful
Hi there - can you please share the data set as well?
perfectly well explained. thank you
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
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.
Thanks a lot! That's really usful :).
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
thank you, wonderful work
How to get total sum below?
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?
We need to pay you for those nice lessons
🙏🏻
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
Can I have your file ?
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