This was such a helpful set of videos - thank you! I used to rely on a bunch of CountIfs in order to have this sort of interactivity, which takes me hours! But your method (creating a copy of the data set with the query function that's dependent on the dashboard drop downs) will be my go-to going forward! This is genius! For those viewers out there who are already pretty familiar with these functions, the key points for me were in Part 2 around the 14 min mark and then part 3 around the 3 min mark.
Simply great, step by step but had to pause and rewind, hope the example sheets can be shared as hands on experience studies on the formulae, thank you.
Hi man, really appreciated your kindness to make those invaluable tutorials for community. It really helps me a lot! When I first saw your video, it took me almost 5 hours to get out of it! So addictive!
Man this is really awesome. I was searching throughout the net for this query since few months, to create a dynamic report at my work place. Today it tried an it came out wonderfully. Thank you very much. Keep up the good work. 😊👏👏👌👍
Hi can somebody help me, mine has an error and it says "Unable to parse query string for Function QUERY parameter 2: Invalid date literal [2020-11-23)]. Date literals should be of form yyyy-MM-dd"
Outstanding tutorials -- I owe you. One place I was tripped up: if I filter a pivot table to exclude blanks then any new values are not automatically included. Example: if my 'Animals' column includes 'cats', 'dogs' and 'ferrets' and some blank rows, if I exclude blanks then all my animals are in the pivot. However, if I now add a row that includes 'birds' it is not included in the pivot unless I manually check it in the Pivot Filter. In effect, deselecting blanks turns the pivot filter into a manual decision what to include.
Thanks for this video mate! This was very helpful. I immediately experimented on 6 different charts and added 5 slicers for order date, sales area, sales rep, sales channel, and items. =)
meeeen i want to say i love you but am straight, but all your videos are just amazing another free leaning masters degree. Good good work i really like this
Would it be possible to build a similar dashboard where users could take advantage of the custom filters option? That way 2 users can look at different dates or regions concurrently
Awesome tutorial videos, I've been learning google sheets with the help of your channel for the past 2 weeks. I got a question, is it possible to make the pivot table and the chart dynamic while adding data in Transactions?
I want to ask a bit. If I use the formula above, I can only select one of the two cases for column C, either all or one of the values Midwestern, Northeastern, right? In case I want to select more than two values but not all values, what should I do? Please answer me, I really need it for my upcoming report
This is so nice, thank you a lot. BUT one question, if I need to filter with more fields. Say I want to filter both Region and Brand, how would the query look like? I tried to combine with more "where" and "and" but without success...
Love your videos they have helped out so much! I have having one issue, The Region section which is Tech for me works fine when my dates are blank. But whenever i choose a date no data is showing and i have a range which should show all my data. Hoping you could possibly help as here is the code i have in right now in the QUERY - =QUERY(Tabsheet!A4:G,"SELECT A, B, C, D, E, F, G, YEAR(A)" & if(COUNTBLANK(HelperFormulas!A1:A3)=3,""," WHERE " & TEXTJOIN(" AND ",TRUE,HelperFormulas!A1:A3)),1). once again love the videos!
@@Jessica_YizhenJiang Hi it's been a while since the first person posted this so I'll reply to you instead. I had the same error, make sure on the helper formula you have the correct Row for the date (My date is on row D and the tutorial is on row A when I first tested it, it worked because I changed the A to D but I forgot to do it on the helper formula one)
how can I filter it to be the month for quarterly reports rather than yearly report in the start and end dates? Also- one of the pivot tables are not updating.
I've gone through this series 2x now, and cannot get my date query to work? The query works fine as long I have no dates entered, but whenever I put dates in on my dashboard, my TempDataSet shows a #VALUE! error. Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " "A "" at line 1, column 49. Was expecting one of: "(" ... "(" I don't even have 49 columns? I've tried all different formats for the column with my dates on the Transactions sheet. Any suggestions would be much appreciated! 😃
=QUERY('Master '!A1:G14,"SELECT A, B, C, D, E, F, G, YEAR(A)" & if(COUNTBLANK(helper!A1:A4)=4,"","WHERE " & TEXTJOIN(" AND ",TRUE,helper!A1:A4)),1) i have issue with count 4 : 1. start date 2. end date 3. region 4. country in above formula i have set count 4 but error is coming and i am not able to resolve this issue (if i take 3 count it's working but if i take 4 or 5 count it's not working can you resolve this issue )
Hello thanks for every lessons of helpful, understable, great experiences and interesting all. I need to know just 1 formula can you guys please help me resolve my problem. If same result same day how to count 1 in formula ?
2.53 I stuck here and saying Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " "F "" at line 1, column 83. Was expecting one of: "(" ... "(" ... Can you please help me here
Rather than have the 2nd criteria be the date, how would I change it from being the date to another list? I've got everything to work with the date but I need it to be based on different criteria from the data
Hi, Thank you for your videos, its really help me and it really works. Yet, i wondering if user can choose multiple, let say I want to see Midwestern, and Northwestern to see comparison between other religion. I hope you can make another video to how to make dashboard with multiple choice . Really appreciate Thank you
Hello, how can i format these tables so they alternate colours ? One line one colour, the other line another colour(but only on the table, not everywhere).. Or how to make it keep their text format whenever I add a new data or change the filters above ? Every time I add some new data or change the current item on the dropdown list, the table changes, but the format doesnt adjust.. I hope u got my doubt, sorry for bad english ;s
Thanks for the video and hinting to use temporary data set to work with. I'm actually not well experienced in Excel and Google Spreadsheets, but I got the idea very quickly. Andthat is the counter part of the video: as for me I felt you over-explained most of the things, so I had to fast-forward the video lots of times :) But thanks anyway :)
Thank you, great tutorial. The problem that I have is that an entire team uses the same Dashboard, so we have multiple users trying to interact with the dropdown for Region and Dates at the same time, would it be possible to somehow allow each individual user to interact with it independently? Thanks,
Hi, if you were building the formula without the data ranges, what would it look like? I have something built but getting hung up on getting the query to do nothing when "All" is selected.
Thx this helps a lot - I repeat it on my own file and works fine. BUT in the DASHBOARD tab add-in ColE the Year as an option to select from and add the formula for it in "Helpfromula" tab in A4. In my data tab the year is in ColE with the formula "=year(A2)" to get the year from the date. BUT I can get the formula in A4 (for the year) in "HelpFormula" tab correct . Can you please assist me with that formula, please?
Thanks, I did get it to work at last - the formula in my "HelpFormula" sheet A4 was not correct and now it is working and the formula is: =IF(ISBLANK(Dashboard!E2),"", "E = " & Dashboard!E2) I also add the months in the Datavalidationlist tab to make the validation not from the data tab . Thx
Hi getting a "Value error" at the point where I tried replacing the formular on the TempDataSheet with the string. Error reads "Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: S"
hi, thanks a lot for how to create dynamic string but when I select any date this error occurs "Unable to parse query string for Function QUERY parameter 2: Invalid date literal [2019-08-15 ]. Date literals should be of form yyyy-MM-dd." whats wrong can you help?
Probably you are not a USA user, so the date format is different. You can create a new “date cell” in another place, like a hidden sheet, and format that "new" cell as USA date format: yyyy-mm-dd. Then, bring the content of the first cell (in your country date format) to that. So, refer your string to that USA date format cell. Worked fine here. Let me know if it works for you too.
This is very Helpful, only issue i currently face is that 'All' works fine, however when i select the person on the drop down, then no data emerges, any idea's?
=IF(Dashboard!A3="all" , "", "C = ' ' " &Dashboard!A3& "'") =QUERY('re-attend data '!A1:F, "SELECT A, B, C, D, E, F" & if(COUNTBLANK('HELPER TAB'!A2:A4)=3,""," WHERE " & TEXTJOIN( " AND ",TRUE,'HELPER TAB'!A2:A4))) Thats what i have - C = ' '" ---- should a name go there?
I'm trying to substitute the below query where date clause with your =if(COUNTBLANK(H15:H16)=2,""," WHERE " & TEXTJOIN(" AND ",TRUE, H15:H16)) but I'm receiving and error. Is it possibly to get it to work with a select like below, that has a group by and an order by? It works perfectly with select * Select J, count(O), avg(O), sum(O) WHERE J >= date"""&text(E2,"yyyy-MM-dd")&""" AND J
Weird, my start and end date is in the correct date format but the query is unable to read my date, and mentioned that the date should be yyyy-MM-dd, but it already is! What is the issue here
@@ExcelGoogleSheets I found out the real issue, after testing, i realized my quote sign has a space before the date which affects the formula as well.... it should not have any space....thanks alot for your vids btw
Excellent! I just a have question, isn't changing query dynamically slow, can't we use conditional filters (referring to the same cells) to make it fast. considering if we are importing the data from other sheets.
Olá professor! Excelente conteúdo! Me ajudou muito! Consegui aplicar numa planilha, no entanto, surgiu uma dúvida. Como corrigir o "erro" #N/D, quando um dos critérios não for atendido? Estou utilizando esta fórmula, onde B1 refere-se à turma (por ex.: 6º ANO) e E1 refere-se à disciplina (por ex.: Matemática): =QUERY(CONSULTA_DB!B4:L;"select * where J Contains '"&B1&"' and F Contains '"&E1&"' ") No entanto, quando seleciono outra disciplina, que não possui registro nessa turma (por ex.: Artes), a célula onde está a função Query retorna a mensagem acima (#N/D) Muito obrigado!!
Thanks for the posting .. Really appreciate your knowledge and lucid presentation. After going through three parts i prepared the following string. My issue is this string works only when I leave start date and end date blank.. The moment i enter date the data vanishes from the dash board. =QUERY(Form!B1:I,"SELECT B,C,D,E,F,G,H,I,YEAR(B) " & if(COUNTBLANK (Sheet4!A1,Sheet4!A2)=2,"","WHERE " &TEXTJOIN("AND ",TRUE,Sheet4!A1:A2)),1) Note: I only need to filter the data according to start and end date, The Sheet 4 in the formula is your helperformula sheet in the video. Please help.
Thanks a lot. Wonderful tuts. I have an issue as i try building my dashboard. I am getting this error and i dont seem to see where the issue is in the formular: Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " "A "" at line 1, column 32. Was expecting one of: "(" ... "(" ... Any help please?
This is the formular: =QUERY(Transactions!A1:Q,"SELECT A,E,L,M,N,year(E)" & if(COUNTBLANK(Helperformulas!A1:A3)=3,""," WHERE " & TEXTJOIN(" AND ",TRUE,Helperformulas!A1:A3)),1)
This has been truly helpful but I can't seem to get past the 16:19 mark because I keep getting a Value error. This is what I have: =QUERY('Revised Data'!A1:U,"select * WHERE S = 'US' OR S = 'CAN'" & if(countblank('Helper Formula'!A1:A3)=3,""," WHERE " & textjoin(" AND ",TRUE,'Helper Formula'!A1:A3)),1) I even tried this one: QUERY('Revised Data'!A1:U,"select *'" & if(countblank('Helper Formula'!A1:A3)=3,""," WHERE " & textjoin(" AND ",TRUE,'Helper Formula'!A1:A3)),1) The different formula works fine on the Dashboard when I followed the video but when I went to put it into the data set, it gave me an error. Any help is greatly appreciated!
I copied and replaced the query formula in 15:56 but it shows an error, I cross-checked it several times with the video and everything seems identical, yet it displays an error :((
@@ExcelGoogleSheets "Select A,B,C,D,E,F,G, Day(A)" & IF(COUNTBLANK('Helper Formulas'!A1:A3)=3,""," Where" & TEXTJOIN(" and ",True,'Helper Formulas'!A1:A3)) This one
As everybody, I'll first say that you are excellent in teaching. Thanks a lot. But, as a non American - I'm French, sorry... - I shall add 2 things. The first one is your voice. Your accent is perfectly and easily understandable -where some Texans are really a nightmare for me. The second one is the syntax of the Query statements, linked to what is generally called the "Locale". The "Locale" is the repository where you store the settings for the date and numeric formats, the currency and other stuff, linked to the country. And it happens that, in French, in the Query statements, the comma , is frequently turned into a semi-comma ; Which can be really confusing - the example shown in the tutorial does not work. This needs to be addressed with a very step by step approach. The kind of approach you have.
It was grateful video..However it throw the Error for the below query =QUERY(GrandTotalByDayByAgent!A3:F,"SELECT A, B, C, D, E, F,F*0.02 " & if(COUNTBLANK(Tips!A1:A3)=3,""," WHERE " & TEXTJOIN(" AND ",TRUE,Tips!A1:A3))",1)")
No sure where I am making the mistake. Also tied with the following =QUERY(GrandTotalByDayByAgent!A3:F,="SELECT A, B, C, D, E, F,F*0.02 " & if(COUNTBLANK(Tips!A1:A3)=3,""," WHERE " & TEXTJOIN(" AND ",TRUE,Tips!A1:A3)),1)
This was such a helpful set of videos - thank you! I used to rely on a bunch of CountIfs in order to have this sort of interactivity, which takes me hours! But your method (creating a copy of the data set with the query function that's dependent on the dashboard drop downs) will be my go-to going forward! This is genius! For those viewers out there who are already pretty familiar with these functions, the key points for me were in Part 2 around the 14 min mark and then part 3 around the 3 min mark.
Simply great, step by step but had to pause and rewind, hope the example sheets can be shared as hands on experience studies on the formulae, thank you.
A very good teacher and an expert. Thank you brother.
This is like magic 😍. I think I have to re-watch from Part 1 to Part 3 again.
Hi man, really appreciated your kindness to make those invaluable tutorials for community. It really helps me a lot! When I first saw your video, it took me almost 5 hours to get out of it! So addictive!
Good job. You are the #1 to explain google sheets.
Just want to sau thankyou... You are my teacher..
Very good explanations and your talking is so clear that i was able to understand even with my poor level of english. Thanks...
Thank you very much for the tutorial, I wish you all the best and be proud on your work!
Amazing! Very useful to me as a beginner in making a dashboard in Google Sheet. Thank you all the best!
Man this is really awesome. I was searching throughout the net for this query since few months, to create a dynamic report at my work place. Today it tried an it came out wonderfully.
Thank you very much. Keep up the good work. 😊👏👏👌👍
Job well done! I finally completed this after two-thinking-days.
MAN! that is so next level.... Awesome stuff, thanks for sharing your knowledge!
Hi can somebody help me, mine has an error and it says "Unable to parse query string for Function QUERY parameter 2: Invalid date literal [2020-11-23)]. Date literals should be of form yyyy-MM-dd"
Outstanding tutorials -- I owe you.
One place I was tripped up: if I filter a pivot table to exclude blanks then any new values are not automatically included.
Example: if my 'Animals' column includes 'cats', 'dogs' and 'ferrets' and some blank rows, if I exclude blanks then all my animals are in the pivot. However, if I now add a row that includes 'birds' it is not included in the pivot unless I manually check it in the Pivot Filter.
In effect, deselecting blanks turns the pivot filter into a manual decision what to include.
Check out my Slicer videos for a solution for this problem.
Thanks for this video mate! This was very helpful. I immediately experimented on 6 different charts and added 5 slicers for order date, sales area, sales rep, sales channel, and items. =)
Really helpful! Excited to experiment with this for a few different sheets concepts. Thank you!
Thank you is very helpfull
Thank you so much! Your video helped me a lot today! 😉
meeeen i want to say i love you but am straight, but all your videos are just amazing another free leaning masters degree. Good good work i really like this
You have a great talent and power to teach others. Keep the Good work going Boss.
Great content, very useful, thank you!
Very helpful, thanks a lot! Next thing for me is to go through this again, working with my own data. Learning by doing.
Would it be possible to build a similar dashboard where users could take advantage of the custom filters option? That way 2 users can look at different dates or regions concurrently
Excellent tutorials. Good work man, very detailed one.
Awesome tutorial videos, I've been learning google sheets with the help of your channel for the past 2 weeks. I got a question, is it possible to make the pivot table and the chart dynamic while adding data in Transactions?
You are just the best man!
Thanks!
You are AMAZING at this. Thank you so much!
Very helpful! Thanks
this is awesome dashboard . This is very helpful for me
thanks a lot . But i want to ask you one thing how can i add
more count for my other use ?
Oh Man, Thank you so much for videos, you are a CRACK!
👍
can we use checklist for the filter?? i mean to select more than 1 value
I want to ask a bit. If I use the formula above, I can only select one of the two cases for column C, either all or one of the values Midwestern, Northeastern, right? In case I want to select more than two values but not all values, what should I do? Please answer me, I really need it for my upcoming report
This is so nice, thank you a lot. BUT one question, if I need to filter with more fields. Say I want to filter both Region and Brand, how would the query look like? I tried to combine with more "where" and "and" but without success...
is there any possible to dashboard filter without importing the data to the sheet?
I need to perform this for 3 sheets in one place
Love your videos they have helped out so much! I have having one issue, The Region section which is Tech for me works fine when my dates are blank. But whenever i choose a date no data is showing and i have a range which should show all my data. Hoping you could possibly help as here is the code i have in right now in the QUERY - =QUERY(Tabsheet!A4:G,"SELECT A, B, C, D, E, F, G, YEAR(A)" & if(COUNTBLANK(HelperFormulas!A1:A3)=3,""," WHERE " & TEXTJOIN(" AND ",TRUE,HelperFormulas!A1:A3)),1). once again love the videos!
I met the same error, is it fixed for you? I cannot find the error :(
@@Jessica_YizhenJiang Hi it's been a while since the first person posted this so I'll reply to you instead. I had the same error, make sure on the helper formula you have the correct Row for the date (My date is on row D and the tutorial is on row A when I first tested it, it worked because I changed the A to D but I forgot to do it on the helper formula one)
What formula to use if we need to get the information by filtering Region
Man you are really great doing this all..... Keep it up 👍👏👏👏
Thank you very much. Very help full
Keep up the good work🤝
are you able to send me the google sheet so i can use it as example?
how can I filter it to be the month for quarterly reports rather than yearly report in the start and end dates?
Also- one of the pivot tables are not updating.
I've gone through this series 2x now, and cannot get my date query to work? The query works fine as long I have no dates entered, but whenever I put dates in on my dashboard, my TempDataSet shows a #VALUE! error. Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " "A "" at line 1, column 49. Was expecting one of: "(" ... "(" I don't even have 49 columns? I've tried all different formats for the column with my dates on the Transactions sheet. Any suggestions would be much appreciated! 😃
same problem
Same problem! I'll be digging through the comments for a solution.
*good tutorial*
=QUERY('Master '!A1:G14,"SELECT A, B, C, D, E, F, G, YEAR(A)" & if(COUNTBLANK(helper!A1:A4)=4,"","WHERE " & TEXTJOIN(" AND ",TRUE,helper!A1:A4)),1)
i have issue with count 4 : 1. start date 2. end date 3. region 4. country
in above formula i have set count 4 but error is coming and i am not able to resolve this issue
(if i take 3 count it's working but if i take 4 or 5 count it's not working can you resolve this issue )
Hello thanks for every lessons of helpful, understable, great experiences and interesting all.
I need to know just 1 formula can you guys please help me resolve my problem.
If same result same day how to count 1 in formula ?
Its really helpful
2.53 I stuck here and saying Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " "F "" at line 1, column 83. Was expecting one of: "(" ... "(" ...
Can you please help me here
string is not working can you please help me out
Rather than have the 2nd criteria be the date, how would I change it from being the date to another list? I've got everything to work with the date but I need it to be based on different criteria from the data
Hi, Thank you for your videos, its really help me and it really works. Yet, i wondering if user can choose multiple, let say I want to see Midwestern, and Northwestern to see comparison between other religion. I hope you can make another video to how to make dashboard with multiple choice . Really appreciate
Thank you
Can you make it filter Multiple regions at once? I am trying to filter by product and want to choose more than 1 product.
I keep getting this error: "Unable to parse query string for Function QUERY parameter 2: NO_COLUMN:" ANy suggestions?
Me too
@@jennababe7777 me too
Hello, how can i format these tables so they alternate colours ? One line one colour, the other line another colour(but only on the table, not everywhere).. Or how to make it keep their text format whenever I add a new data or change the filters above ? Every time I add some new data or change the current item on the dropdown list, the table changes, but the format doesnt adjust.. I hope u got my doubt, sorry for bad english ;s
Thanks for the video and hinting to use temporary data set to work with. I'm actually not well experienced in Excel and Google Spreadsheets, but I got the idea very quickly. Andthat is the counter part of the video: as for me I felt you over-explained most of the things, so I had to fast-forward the video lots of times :) But thanks anyway :)
Amazing!! Thank you very much!
Thank you, great tutorial. The problem that I have is that an entire team uses the same Dashboard, so we have multiple users trying to interact with the dropdown for Region and Dates at the same time, would it be possible to somehow allow each individual user to interact with it independently? Thanks,
Not sure what to say. Did you try Google Data Studio?
You could create separate dashboard sheets for each individual. Perfectly identical just with different names.
Couldn’t they just use different filter views?
How to do things like this with numeric variables. I tried to do exactly as the video but it wouldn't work
Hi, if you were building the formula without the data ranges, what would it look like? I have something built but getting hung up on getting the query to do nothing when "All" is selected.
Hard to say without looking at your particular case. You could share a spreadsheet with your setup.
or you can use add on 'Dicers', right ? :)
Harika .. çok güzel anlatım. Teşekkürler...
hi can u please send me the google sheet?
Thx this helps a lot - I repeat it on my own file and works fine. BUT in the DASHBOARD tab add-in ColE the Year as an option to select from and add the formula for it in "Helpfromula" tab in A4. In my data tab the year is in ColE with the formula "=year(A2)" to get the year from the date. BUT I can get the formula in A4 (for the year) in "HelpFormula" tab correct . Can you please assist me with that formula, please?
Thanks, I did get it to work at last - the formula in my "HelpFormula" sheet A4 was not correct and now it is working and the formula is:
=IF(ISBLANK(Dashboard!E2),"", "E = " & Dashboard!E2)
I also add the months in the Datavalidationlist tab to make the validation not from the data tab .
Thx
Hi getting a "Value error" at the point where I tried replacing the formular on the TempDataSheet with the string. Error reads "Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: S"
double-check your formula, as you might've copied any unnecessary signs such as = etc
GRACIAS POR TU AYUDA
hi,
thanks a lot for how to create dynamic string but when I select any date this error occurs "Unable to parse query string for Function QUERY parameter 2: Invalid date literal [2019-08-15 ]. Date literals should be of form yyyy-MM-dd."
whats wrong can you help?
Probably you are not a USA user, so the date format is different.
You can create a new “date cell” in another place, like a hidden sheet, and format that "new" cell as USA date format: yyyy-mm-dd. Then, bring the content of the first cell (in your country date format) to that. So, refer your string to that USA date format cell. Worked fine here. Let me know if it works for you too.
This is very Helpful, only issue i currently face is that 'All' works fine, however when i select the person on the drop down, then no data emerges, any idea's?
Possibly missing single quotes around the name?
=IF(Dashboard!A3="all" , "", "C = ' ' " &Dashboard!A3& "'")
=QUERY('re-attend data '!A1:F, "SELECT A, B, C, D, E, F" & if(COUNTBLANK('HELPER TAB'!A2:A4)=3,""," WHERE " & TEXTJOIN( " AND ",TRUE,'HELPER TAB'!A2:A4)))
Thats what i have - C = ' '" ---- should a name go there?
I'm trying to substitute the below query where date clause with your =if(COUNTBLANK(H15:H16)=2,""," WHERE " & TEXTJOIN(" AND ",TRUE, H15:H16)) but I'm receiving and error. Is it possibly to get it to work with a select like below, that has a group by and an order by? It works perfectly with select *
Select J, count(O), avg(O), sum(O) WHERE J >= date"""&text(E2,"yyyy-MM-dd")&""" AND J
Weird, my start and end date is in the correct date format but the query is unable to read my date, and mentioned that the date should be yyyy-MM-dd, but it already is! What is the issue here
Seems like you forgot to use TEXT function around your dates.
@@ExcelGoogleSheets I found out the real issue, after testing, i realized my quote sign has a space before the date which affects the formula as well.... it should not have any space....thanks alot for your vids btw
@@BF_official_my thanks brother it helped me solve my issue
@@ArINxCla wow bro, it's been 4 years since this comment glad it helps hahaha
I've learned a lot however I can't seem to filter the date. Any tips?
Make sure the dates are proper dates and the column is formatted as date.
Hey man, first thanks for creating these videos, but i have a question: How do I create the filters when only using text and not using dates?
ruclips.net/video/nLW8SerwnJo/видео.html
@@ExcelGoogleSheets That kinda worked, but how can i put it on the Dashboard?
I managed to make it, thanks.
Excellent! I just a have question, isn't changing query dynamically slow, can't we use conditional filters (referring to the same cells) to make it fast. considering if we are importing the data from other sheets.
Olá professor! Excelente conteúdo! Me ajudou muito!
Consegui aplicar numa planilha, no entanto, surgiu uma dúvida.
Como corrigir o "erro" #N/D, quando um dos critérios não for atendido?
Estou utilizando esta fórmula, onde B1 refere-se à turma (por ex.: 6º ANO) e E1 refere-se à disciplina (por ex.: Matemática):
=QUERY(CONSULTA_DB!B4:L;"select * where J Contains '"&B1&"' and F Contains '"&E1&"' ")
No entanto, quando seleciono outra disciplina, que não possui registro nessa turma (por ex.: Artes), a célula onde está a função Query retorna a mensagem acima (#N/D)
Muito obrigado!!
Thanks for the posting .. Really appreciate your knowledge and lucid presentation. After going through three parts i prepared the following string. My issue is this string works only when I leave start date and end date blank.. The moment i enter date the data vanishes from the dash board.
=QUERY(Form!B1:I,"SELECT B,C,D,E,F,G,H,I,YEAR(B) " & if(COUNTBLANK (Sheet4!A1,Sheet4!A2)=2,"","WHERE " &TEXTJOIN("AND ",TRUE,Sheet4!A1:A2)),1)
Note: I only need to filter the data according to start and end date, The Sheet 4 in the formula is your helperformula sheet in the video.
Please help.
same problem I have...
Okay, you have to change the "", the other way around, it switched up when copied to "HelperFormulas". That will do it ;-)
Thanks a lot. Wonderful tuts.
I have an issue as i try building my dashboard. I am getting this error and i dont seem to see where the issue is in the formular:
Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " "A "" at line 1, column 32. Was expecting one of: "(" ... "(" ...
Any help please?
This is the formular:
=QUERY(Transactions!A1:Q,"SELECT A,E,L,M,N,year(E)" & if(COUNTBLANK(Helperformulas!A1:A3)=3,""," WHERE " & TEXTJOIN(" AND ",TRUE,Helperformulas!A1:A3)),1)
doesn't DATE TEXT together cancel each other? why not just go with A>= '" & C2 & "'?
This has been truly helpful but I can't seem to get past the 16:19 mark because I keep getting a Value error.
This is what I have:
=QUERY('Revised Data'!A1:U,"select * WHERE S = 'US' OR S = 'CAN'" & if(countblank('Helper Formula'!A1:A3)=3,""," WHERE " & textjoin(" AND ",TRUE,'Helper Formula'!A1:A3)),1)
I even tried this one: QUERY('Revised Data'!A1:U,"select *'" & if(countblank('Helper Formula'!A1:A3)=3,""," WHERE " & textjoin(" AND ",TRUE,'Helper Formula'!A1:A3)),1)
The different formula works fine on the Dashboard when I followed the video but when I went to put it into the data set, it gave me an error. Any help is greatly appreciated!
Vera mari
I copied and replaced the query formula in 15:56 but it shows an error, I cross-checked it several times with the video and everything seems identical, yet it displays an error :((
What's your formula Alex?
@@ExcelGoogleSheets "Select A,B,C,D,E,F,G, Day(A)" & IF(COUNTBLANK('Helper Formulas'!A1:A3)=3,""," Where" & TEXTJOIN(" and ",True,'Helper Formulas'!A1:A3)) This one
@@alexrao8671 This last part doesn't seem to make sense TEXTJOIN(" and ",True,'Helper Formulas'!A1:A3))
go over that part again.
As everybody, I'll first say that you are excellent in teaching.
Thanks a lot.
But, as a non American - I'm French, sorry... - I shall add 2 things.
The first one is your voice.
Your accent is perfectly and easily understandable -where some Texans are really a nightmare for me.
The second one is the syntax of the Query statements, linked to what is generally called the "Locale".
The "Locale" is the repository where you store the settings for the date and numeric formats, the currency and other stuff, linked to the country.
And it happens that, in French, in the Query statements, the comma , is frequently turned into a semi-comma ;
Which can be really confusing - the example shown in the tutorial does not work.
This needs to be addressed with a very step by step approach.
The kind of approach you have.
Countblank is not working on those formulas
He used the formula
A. Text join
B. Query
C.if
D.is blank
Nice!
I want to hire you
Would you be able to send me the google sheet; so I use it while creating my own dashboard?
It was grateful video..However it throw the Error for the below query =QUERY(GrandTotalByDayByAgent!A3:F,"SELECT A, B, C, D, E, F,F*0.02 " & if(COUNTBLANK(Tips!A1:A3)=3,""," WHERE " & TEXTJOIN(" AND ",TRUE,Tips!A1:A3))",1)")
No sure where I am making the mistake. Also tied with the following
=QUERY(GrandTotalByDayByAgent!A3:F,="SELECT A, B, C, D, E, F,F*0.02 " & if(COUNTBLANK(Tips!A1:A3)=3,""," WHERE " & TEXTJOIN(" AND ",TRUE,Tips!A1:A3)),1)