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.
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.
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.
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?
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?
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 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.
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.
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.
![BLACK BAG - Official Trailer [HD] - Only in Theaters March 14](http://i.ytimg.com/vi/Du0Xp8WX_7I/mqdefault.jpg)
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.
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.
Great content. Thx for sharing this. Learning a lot!
This is spectacular! It works!
I love you master................... you r awesome
Fantastic Great job!
Hi there - loving all your lessons they great. Can you please share the sample sheets you using?
min 05:43 the difference between PIVOT (by columns) and GROUP BY (by rows)... Muchas gracias!
10:40 select by year. Very useful tip.
Great step by step explanation!
Awsome❤
Is possible to create a column with the row total, like in pivot table?
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.
Everytime I use this, a blank row or colum gets created. Any idea why
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 :)
Greetings from Brazil
Thanks for the video! is there a way to display the months as name of month instead of numbers?
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.
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 possible to use a SELECT statement inside SUM in a QUERY function?
Wonderful
Is there any way to show the values of a pivot but hide the pivot column headers?
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
What about the no_format options clause in google sheets
another great video, thanx man
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?
Thank a lot for you tutorials!
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?
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
Can you please make a video on query progressive column
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
perfectly well explained. thank you
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.
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
How do I convert the MONTH numbers to actual dates?
thank you, wonderful work
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
Thanks a lot! That's really usful :).
Hi there - can you please share the data set as well?
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?
How to get total sum below?
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