Hi Very helpful, thank you! I have an issue where I am using query to pull names from one table into a secondary table but when a new name is added to the first table and it pulls through to the secondary table, it doesnt reconise the new rows and doesnt auto format? Why is this? Can I fix it?
Awesome Playlist. I've had so many "AHAA!" moments watching your courses. Excellent! I have a problem where I want to report the ONLY the most profitable sales number out of 3 categories of items, made by 10 different brands, all of which have sales in the same set of 20 cities. So for Los Angeles maybe NIKE's most profitable sales category is shoes at $3m and Ralph Lauren's most profitable sales category is hats at $1.2m, but in Dallas NIKE's most profitable category is shirts $2m and Ralph Lauren's most profitable sales category is shoe's at $4m. I could make a report that shows all of the categories and just visually locate the most profitable one, but I want to only show the most profitable one and ignore the others. The brands would have different categories for each city because the demographics are different. Any guidance is appreciated. I'd like to avoid writing a script with some kind of loop.
Sound like you need to create one query to get your profit by category and then use it as a subquery to get the most profitable ones out of that. Watch my Query Subquery video for to see how you run subqueries.
@Learn Google Spreadsheets How to have total sum below the transactions? Also is it possible to have sub-total below the transactions called using query
Hello! Thank you for video. I've a question about "options no_values" clause. Could you help me? I have 2 very large tables with several thousands of rows and only 2 columns each. First column is 'datetime' and the second is some parameter. In the table A datetime had been recorded each 5 minutes, and in table B - each 20 minutes. I want to merge these tables into one with datetime every 1 hour.. Something like this: Datetime (YYYY-MM-DD HH:00:00) AVG(ParameterA) AVG(ParameterB) So what’s am I doing? 1. Getting data from table A with query: Select MIN(A), AVG(C), Year(A), Month(A)+1, Day(A), Hour(A) group by Year(A), Month(A)+1, Day(A), Hour(A) Order by MIN(A) Format MIN(A) 'YYYY.MM.DD HH:00:00' 2. It's give me exactly that I need! Every row have been grouped by hour and everything seems to be ok. 3. Than I’m getting data from Table B with the pretty much the same query. 4. And after all I’m trying to merge data by VLOOKUP (hour-by-hour). 5. And here comes the problem! Although my datatime columns looks very nice and clean (because of format) it has different Min(A) values, because data was written in different minutes of hour. So I have cells in Tables A&B looks like “2017.06.15 01:00:00”, but if I’m checking the values of these cells, it is “2017.06.15 01:04:00” and “2017.06.15 01:09:00” for tables A and B. And because the values is different, VLOOKUP is not working. ( 6. I’ve found information about “options no_values” clause, but it is not changing anything in my case… What I’m doing wrong? Thank you!
hello i have a problem labeling a column header with a Date teken from a cell data validation, the result is a number and not a year this is my code =QUERY(TB_vendite;"SELECT sum(I) where month(A)+1 ="&MESE($B$3)&" and year(A)="&ANNO($B$2)&" group by M label sum(I) '"&($B$2)&"'") where b2 is the cell data validation....the data validation contain 2023 but the label return 44563 i tryied to format by menu.....but nothing...helpe me please
Amigo, preciso de ajuda: " =QUERY( QUERY('DF_Financeiro!'!A2:P;"Select Year(A),Month(A)+1,N,P,O Where Year(A) = 2023";1); "Select Col1,Col2,Col3,Sum(Col4),Col5 Group by Col1,Col2,Col3,Col5 Order by Col1,Col2,Col3";1) " Essa função acima funciona muito bem... Mas gostaria que o "Month(A)+1" desse como produto o nome do mês por extenso (Janeiro, fevereiro...) porém quando uso " Format Month(A)+1 'mmmm' ele simplesmente falha e me trás apenas Dezembro e Janeiro em meses que não deveria.
Hello. I trying some SQL like 'SELECT A, B/100,1 GROUP BY A', and "100,1" is a number in a cell. The result is 3 collumns. First is A, the second is "quotient(sum xxx())" and the third is "yy()" where yy is the decimal part of the B/100,1... I solve using 'B/" & TRUNC(100,1) & ' ...not the best.
Hi. Great content btw. Please keep it coming :) My question. =QUERY('Booking Sheet'!$A$3:$F,"select B where D='"&E14 & "' limit 2",0) Let's say you have set your display results limit to 2 because you know in some cases there is more than one match. How do I get the results to display horizontally in the next cells instead of below each other? I'm asking you because I actually had something like this a few years ago but can not for the life of me remember how I did it. Your help would be greatly appreciated.
Buen video. Pregunta como puedo hacer 2 filtros en el query, tengo que filtrar por fecha y por letra QUERY(plaguicidas!A4:T;"select A,H,J,K,P,S,R,Q WHERE E='"&B2&"' AND Q>1 ORDER BY A,S";-1)
Can you just help me to correct my Formula, I am getting error when i am putting functions of label =query(A8:AG,"Select A,J,D,U,G,I,K,R,Count(Z),Count(AE),Count(AG), Count(Z)-Count(AE), Count(Z)-Count(AG) where X='SW' group by A,J,D,U,G,I,K,R, lable Count(Z)-Count(AE) 'Jt Count Fitup Bal "',1)
At 6:58 you show YEAR(A) parameter and get what you want. When I try QUERY fails (#VALUE!) and I am told A does not have a DATE or DATETIME format. But it does: I checked. And regular function =YEAR(A) works fine in my sheet. What am I missing?
@@ExcelGoogleSheets Thanks for quick response :-) I have triple checked that entire column is only dates and in DATE format (except for first row which has the Column label which is why QUERY has 1 as last parameter). No blanks in column. Each cell contains MM/DD/YYYY formatted numbers. And =MONTH() does work correctly for the entire column. I must be blind to something subtle or basic? Btw: I am learning so much from your many videos: Thank you!
Best video on Query function available on YT. Thank you man
Wow, easy to understand, easy to refer back to. Great job
I binge your query videos list. Really helpful for me. You're the best!
was struggling with compstat homework! THANK YOU!!!!
excellent post
You are great
Tq for sharing
Great video.
From Italy
Thanks for the video, great job
Sooo good. Thanks!
Amazing explanation. Thank you.
Hi Very helpful, thank you! I have an issue where I am using query to pull names from one table into a secondary table but when a new name is added to the first table and it pulls through to the secondary table, it doesnt reconise the new rows and doesnt auto format? Why is this? Can I fix it?
Why haven't used the 'where condition' in the first 'query'? 16:32
any idea to rename query pivot label ? i still finding how to rename header label from the result query pivot
Are there commands to format columns left, center or right justified and the Header bold, centered, etc?
Awesome Playlist. I've had so many "AHAA!" moments watching your courses. Excellent! I have a problem where I want to report the ONLY the most profitable sales number out of 3 categories of items, made by 10 different brands, all of which have sales in the same set of 20 cities. So for Los Angeles maybe NIKE's most profitable sales category is shoes at $3m and Ralph Lauren's most profitable sales category is hats at $1.2m, but in Dallas NIKE's most profitable category is shirts $2m and Ralph Lauren's most profitable sales category is shoe's at $4m. I could make a report that shows all of the categories and just visually locate the most profitable one, but I want to only show the most profitable one and ignore the others. The brands would have different categories for each city because the demographics are different. Any guidance is appreciated. I'd like to avoid writing a script with some kind of loop.
Sound like you need to create one query to get your profit by category and then use it as a subquery to get the most profitable ones out of that. Watch my Query Subquery video for to see how you run subqueries.
@Learn Google Spreadsheets
How to have total sum below the transactions? Also is it possible to have sub-total below the transactions called using query
Hello!
Thank you for video.
I've a question about "options no_values" clause. Could you help me? I have 2 very large tables with several thousands of rows and only 2 columns each. First column is 'datetime' and the second is some parameter.
In the table A datetime had been recorded each 5 minutes, and in table B - each 20 minutes. I want to merge these tables into one with datetime every 1 hour.. Something like this:
Datetime (YYYY-MM-DD HH:00:00) AVG(ParameterA) AVG(ParameterB)
So what’s am I doing?
1. Getting data from table A with query:
Select MIN(A), AVG(C), Year(A), Month(A)+1, Day(A), Hour(A)
group by Year(A), Month(A)+1, Day(A), Hour(A) Order by MIN(A)
Format MIN(A) 'YYYY.MM.DD HH:00:00'
2. It's give me exactly that I need! Every row have been grouped by hour and everything seems to be ok.
3. Than I’m getting data from Table B with the pretty much the same query.
4. And after all I’m trying to merge data by VLOOKUP (hour-by-hour).
5. And here comes the problem! Although my datatime columns looks very nice and clean (because of format) it has different Min(A) values, because data was written in different minutes of hour.
So I have cells in Tables A&B looks like “2017.06.15 01:00:00”, but if I’m checking the values of these cells, it is “2017.06.15 01:04:00” and “2017.06.15 01:09:00” for tables A and B. And because the values is different, VLOOKUP is not working. (
6. I’ve found information about “options no_values” clause, but it is not changing anything in my case… What I’m doing wrong?
Thank you!
hello i have a problem labeling a column header with a Date teken from a cell data validation, the result is a number and not a year this is my code =QUERY(TB_vendite;"SELECT sum(I) where month(A)+1 ="&MESE($B$3)&" and year(A)="&ANNO($B$2)&" group by M label sum(I) '"&($B$2)&"'")
where b2 is the cell data validation....the data validation contain 2023 but the label return 44563 i tryied to format by menu.....but nothing...helpe me please
Amigo, preciso de ajuda:
"
=QUERY(
QUERY('DF_Financeiro!'!A2:P;"Select Year(A),Month(A)+1,N,P,O Where Year(A) = 2023";1);
"Select Col1,Col2,Col3,Sum(Col4),Col5 Group by Col1,Col2,Col3,Col5 Order by Col1,Col2,Col3";1)
"
Essa função acima funciona muito bem... Mas gostaria que o "Month(A)+1" desse como produto o nome do mês por extenso (Janeiro, fevereiro...) porém quando uso " Format Month(A)+1 'mmmm' ele simplesmente falha e me trás apenas Dezembro e Janeiro em meses que não deveria.
Start going back in the playlist to like those that you missed, you'll be greeted by his Ok so in this video phrase like 10 times
lol
Hello. I trying some SQL like 'SELECT A, B/100,1 GROUP BY A', and "100,1" is a number in a cell. The result is 3 collumns. First is A, the second is "quotient(sum xxx())" and the third is "yy()" where yy is the decimal part of the B/100,1... I solve using 'B/" & TRUNC(100,1) & ' ...not the best.
🙏🏻
Hi. Great content btw. Please keep it coming :)
My question.
=QUERY('Booking Sheet'!$A$3:$F,"select B where D='"&E14 & "' limit 2",0)
Let's say you have set your display results limit to 2 because you know in some cases there is more than one match. How do I get the results to display horizontally in the next cells instead of below each other?
I'm asking you because I actually had something like this a few years ago but can not for the life of me remember how I did it.
Your help would be greatly appreciated.
Use TRANSPOSE function
Please how could I add a new column that not exist in the data source ?
Buen video. Pregunta como puedo hacer 2 filtros en el query, tengo que filtrar por fecha y por letra QUERY(plaguicidas!A4:T;"select A,H,J,K,P,S,R,Q WHERE E='"&B2&"' AND Q>1 ORDER BY A,S";-1)
Can you just help me to correct my Formula, I am getting error when i am putting functions of label
=query(A8:AG,"Select A,J,D,U,G,I,K,R,Count(Z),Count(AE),Count(AG), Count(Z)-Count(AE), Count(Z)-Count(AG) where X='SW' group by A,J,D,U,G,I,K,R, lable Count(Z)-Count(AE) 'Jt Count Fitup Bal "',1)
looks like you didn't spell this right lable
@@ExcelGoogleSheetsGot it, thanks for help.
Hi! I'm from Brazil and love your videos. Use query function and pivot , how could I calculate a total for each line? tks again!
Have you check calculated fields in QUERY? I think that's want you want/
At 6:58 you show YEAR(A) parameter and get what you want. When I try QUERY fails (#VALUE!) and I am told A does not have a DATE or DATETIME format. But it does: I checked. And regular function =YEAR(A) works fine in my sheet. What am I missing?
For this to work the entire column has to have dates in it and nothing else other than dates. Also the column needs to be formatted as date.
@@ExcelGoogleSheets Thanks for quick response :-) I have triple checked that entire column is only dates and in DATE format (except for first row which has the Column label which is why QUERY has 1 as last parameter). No blanks in column. Each cell contains MM/DD/YYYY formatted numbers. And =MONTH() does work correctly for the entire column. I must be blind to something subtle or basic? Btw: I am learning so much from your many videos: Thank you!
Does your QUERY function have 1 as the last argument?
Just noticed you've already mentioned it does. It's hard to tell without looking at it. It's probably something minor ath's hard to notice.