It was not the main topic, but finally I learned how to use the label and format in the query function! Thank you!!
@robertmaluka27635 жыл бұрын
Funny I just came across Slicer in the drop down and I had no idea what it really was. You gave me some great ideas. Thanks again for a great video
@marianoandresbalino88693 жыл бұрын
Dude this is actually mind blowing. Thank you so much for taking your time to share your knowledge. I’m actually learning a lot, keep up the great work 👍🏻
@cassioschneider9224 жыл бұрын
one thing i found out and worked great for me: i like the format to be always in order, so i like this format: *' MM - MMM '* it shows like this: *01 - jan.* 02 - feb. etc cheers
@ExcelGoogleSheets4 жыл бұрын
Good Idea!
@aarontovar84553 жыл бұрын
Man, I love your videos. I love how you explain everything! Keep up the great work!!
@XCelVietnam3 жыл бұрын
me too, he's such a great guru and i am so much in love with his channel.
@queenkai9522 Жыл бұрын
Thank you for your videos. They're very helpful.
@martinachoi78433 жыл бұрын
is there a reason why we can't make charts directly from pivot table?
@EntreprendreetVivre Жыл бұрын
Excellent video with all the info I was looking for! Thanks a million!❤
@Kevin_Wakuze4 жыл бұрын
This was extremely helpful. Thank you very much for sharing this video.
@wormemc3 жыл бұрын
Thanks a ton super useful
@fzmhn Жыл бұрын
Merci pour ce précieux partage bonne continuation 😊
@AdamLundquist3 жыл бұрын
That video was amazing!
@ExcelGoogleSheets3 жыл бұрын
Thank you!
@rogerhartley66304 жыл бұрын
Excellent set of videos, thanks for taking the time to make these!
@mayanksinha408411 ай бұрын
hi, I am new to working with sheets, slicer works for me in the sheet. But it stays static once I publish it and I am unable to do any action on it. is there any way to make it so that their is a dynamic filter available for users who are viewing from the published link?
@PanteraRossa Жыл бұрын
I have large data sets that I have to breakdown into quarterly 3-month sheets because of the restraints on cells on a sheet. Is there a way to combine all these sheets into a single pivot table like this?? Or are you limited to one single data set per pivot table?
@ArchMageRaven Жыл бұрын
Is there any way to use slicers to exclude weekend days?
@numannor Жыл бұрын
Great Video! If I'd like to capture the date from Column A together with Year and Month, what should I do? It says the column toDate(A) can only be used once.
@ExcelGoogleSheets Жыл бұрын
Use year(A) , month(A) ?
@betzabefunes1513 Жыл бұрын
Thanks for the video was very helpful 🫶🏻🫶🏻
@gregoryunderwood41215 жыл бұрын
Brillant! Once again you're a lifesaver!! Thank you!!
@mattmorrisson96075 жыл бұрын
This is some fantastic content! Thanks for making this!
@ManuelaGuarneri4 жыл бұрын
Hello, this is great. I need to do something very similar, but I would need to have a stacked bar chart (for example bar chart of the sales rep with the bar divided per brand). Is it possible to do from the main database without having to do a pivot with brand on the column and creating the chart from the pivot? Thank you for any help!!
@pichit.raetai5 жыл бұрын
thank you
@sanyogsharma21954 жыл бұрын
How to add charts for kpi
@WheelieTruther2 жыл бұрын
Is there a reason why you copy the data rather than doing it all from the Subtotal sheet?
@ExcelGoogleSheets2 жыл бұрын
This way it will auto generate new columns as you keep adding new data to the source.
@vvdd9032 жыл бұрын
very kool very kool. thanks much
@ExcelGoogleSheets2 жыл бұрын
Glad you liked it!
@antonellafavaro21163 жыл бұрын
how can I do to save the data selected on the slicer in order to be saved the next time i open the dashboard?
@SUMIT42234 жыл бұрын
Thanks for sharing.
@AdamLundquist4 жыл бұрын
Is there a way we can get access to the sheet to practice on?
@peterskalla23214 жыл бұрын
This is a fantastic overview, helping me extend my spreadsheet skills to SQL. Thank you! Question -- in my slicer on similar data, any time I set the MONTH slicer to other than all selected, my pivot tables give me a #REF error with the message: "Error : PIVOT evaluates to an out of bounds range." Can you help with how I resolve this? Thank you.
@samiranjoseph97934 жыл бұрын
Ensure that you edit/update the slicer data range from the pivot table range to actually include the extended pseudo - month column e.g. datacopy!A1:J24
@cherylpokemon63764 жыл бұрын
Hello, thank you so much for your video! I have tried implementing what you have done and I am having trouble having the pivot table update. So I have a slicer for year and month but when I add new dates, it does not show up in the pivot table or slicers. Any advice would be appreciated! :)
@cherylpokemon63764 жыл бұрын
Never mind, just figured it out! Didn't show up because of the date filter status
@jonathanpjaime5 жыл бұрын
great tutorial as always. may I know how to change the date to week number? thanks
@sayanta.k5 жыл бұрын
You can count the differences between a particular date from the beginning of the year and divide it by seven, then round it off to get the week. check out this function called "dateDiff " developers.google.com/chart/interactive/docs/querylanguage#scalar-functions I think this maybe the only way to do this in a Query.
@miket16383 жыл бұрын
Has anyone found a way to show what is selected in the slicer? In excel it shows the value. In sheets it only shows 1 of 12 or whatever.
@amirka252 жыл бұрын
I'm curious about that too!
@AllinOne-cr2cs3 жыл бұрын
slicer not working after publish the dashboard
@simplesio99864 жыл бұрын
I am sorting my data according to month with all the formulas given in the video..But when i am using pivote table and adding month to rows...instead of showing total value for a month like May June July It is showing May May May May June June June June Please help
@ExcelGoogleSheets4 жыл бұрын
You should use your original date column and group the dates.
@AREPL4 жыл бұрын
How can I make slicers usable for public, but protect my chart? If somebody tries to use it while being not loged in, it says that he has no permission.
@ExcelGoogleSheets4 жыл бұрын
Use Google Data Studio instead. kzbin.info/www/bejne/nabKXouHZa59d5o
@ahmedabdelkhalek34894 жыл бұрын
thank you, really helpful :) I would like to ask why did you use the query function instead of adding helper columns in the SUBTOTAL sheet? Would query be faster than the helper columns formulas? and for a larger dataset, wouldn't it slow the sheet?
@ExcelGoogleSheets4 жыл бұрын
You should use whatever method works best with your data. QUERY just keeps things updated, but it has its problems.
@WheelieTruther2 жыл бұрын
Is there a SELECT WEEK function as well?
@ExcelGoogleSheets2 жыл бұрын
No such function.
@WheelieTruther2 жыл бұрын
@@ExcelGoogleSheets Managed to hack this together by using concatenate and the WEEKDAY function outside of a query. "=IF(D2="","",(CONCATENATE(TEXT((D2-WEEKDAY(D2,2))+1,"mmm dd")," - ",(TEXT((D2+7)-(WEEKDAY(D2,2)),"mmm dd"))))"
@ExcelGoogleSheets2 жыл бұрын
That makes sense.
@francelinoximenes94974 жыл бұрын
Why don't we use a pivot table to directly create a chart? What effect on the data when creating a chart from the pivot table?
@ExcelGoogleSheets4 жыл бұрын
Currently it picks up the range as a source not the pivot table, which means as your data changes and your pivot table updates it will not update the chart the right way.
@mickegtz3 жыл бұрын
Mmm...I don't think you don't know, but I'm not sure why you are doing all the process from zero when creating the second and subsequent pivot tables...instead, you could copy the first cell (left, top) of the pivot already existing and paste it anywhere within the spreadsheet? (obviously without overlapping to each other). Again, I don't think you don't know but I hope it help someone here.
@RahilChoudhary4 жыл бұрын
Thanks
@josephleslie007Ай бұрын
Cool ❤
@speedwaylabsdev4 жыл бұрын
thanks!
@gilangpuja10394 жыл бұрын
hai.. is there any way to reset all those filter ?
@gilangpuja10394 жыл бұрын
reset all filter with only one click
@sandysameh-it5wp Жыл бұрын
Great Video I try more and more to change label name from *day(Timestamp)* to *DAY* but no action done when i try more troubleshoot always gave me Error ; I try use this function =QUERY(Sheet1!A1:T,"SELECT A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T, DAY(A) ,YEAR(A), toDate(A) Label YEAR(A) 'Year', toDate(A) 'Month' format toDate(A) 'MMM'",1) But How i add to Change label Day???????????????????????????????????????????????? waiting your Help and many thanks
@mask752605 жыл бұрын
Awesome
@metasal4 жыл бұрын
Count of Sales is 1399 if Row 1 is headers was selected
@makmurhasanuddin31514 жыл бұрын
Hi ... I'm including your subscribe ... I beg for your help I have a dashboard that consists of several slicers, pivots, charts (line charts, meter diagrams, and score card diagrams) and data validation. when it will be exported, the values in each chart change ... For your information, I use the help sheet for some simple formulas to complete my chart. Please help
@ahmadghiffari915 жыл бұрын
great
@jmack87able4 жыл бұрын
Hi, thank you for the video it is important content very well done. I am getting a parsing error trying to write month to the toDate field from column A. My Query line is =QUERY(Sheet1!A1:D,"SELECT A, B, C, D, YEAR(A), toDate(A) format toDate(A) 'MMM', DAY(A), HOUR(A), MINUTE(A), SECOND(A)", 1). The error returned is "Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " "," ", "" at line 1, column 69. Was expecting one of: ... "*" ... "+" ... "-" ... "/" ... "%" ... Any idea whats wrong, thank you!
@Battlem0nk3 жыл бұрын
format toDate(A) 'MMM' should be at the end of the query after all col are selected =QUERY(Sheet1!A1:D,"SELECT A, B, C, D, YEAR(A), toDate(A) , DAY(A), HOUR(A), MINUTE(A), SECOND(A) format toDate(A) 'MMM'", 1)