Video

Hi! If the row and column dimensions aren't aligning correctly, it might be due to the data type or formatting in your data source. Double-check that the fields you're using are correctly set as dimensions, and ensure there's no mismatch in data types (like mixing text with numbers). Also, make sure you've selected the right fields for rows and columns in your pivot table setup.

@@coupleracademy I dont know what I did but cleaning up the data and my issue is resolved. Thanks.

Hi! Sorry if it was a bit more advanced than you expected, but glad you stuck with it! 😅 Maybe you’re leveling up faster than you think. Thanks for watching!

Hi! Drill-down with dates in a pivot table requires that your date field is properly set up as a "Date" type in your data source. Additionally, ensure that your date hierarchy is configured correctly. To do this: - Check Date Type: In your data source, confirm that the date field is recognized as a "Date" type. - Create Date Hierarchy: If the default hierarchy isn't working, consider creating a custom date field that breaks down into Year, Quarter, Month, and Day. This helps the pivot table understand the drill-down structure. This should allow you to drill down effectively from broader to more specific time frames.

Sorry to hear you're having trouble! If you’re getting a #REF! error even with permission, try these steps: - Double-check permissions: Ensure you have "Viewer" or higher access to the linked sheet. - Re-authorize: Sometimes, re-adding the IMPORTRANGE function can prompt the authorization request again. Just enter the formula and allow access when prompted. - Try incognito mode: Sometimes, cache issues can interfere, so try it in an incognito window. If it still doesn’t work, there might be a permissions issue with the specific spreadsheet. Hope this helps!

Thanks for your question! To calculate profit/loss with a negative value when expenses are higher than total earnings, try this formula: =A2 - (A3 + A4) This will subtract your total expenses from your earnings, and if expenses are higher, it will show a negative value (loss).

You're welcome!

You are welcome! Don't forget to subscribe to our channel so you will not miss another great tutorial.

Well, in such a case, the setup heavily depends on the organization of the data you use. But it seems like you need to add 'keyword' to rows, possibly 'dates' to columns, and use 'rank' as a metric. I hope this will help or at least guide you in the right direction.

@@coupleracademy but there is no field for rank, the thing is my data sources is from search console - site impression.

@@coupleracademy Step by step instruction to build the following SEO REPORT IN Google looker studio from the search console dataset CATEGORIZED Average position (LIKE BELOW AND COUNTS by each month

@@semponmanickam5924 We have a free SEO dashboard template. Maybe it will suit your needs www.coupler.io/marketing-dashboards/google-search-console-dashboard

@@coupleracademy thank you 🙏 let me look into it

Whoa, we are glad to hear that! Don't forget to subscribe to our channel for even more lifesaving pieces of advice 🙃

Glad you like it!

Thanks! Such comments inspire us to make more tutorials 🫶

What do you find especially confusing? It is good to know to take into account in further tutorials.

Well, you can try using one of those dashboard templates (they are free; links are in the description) to understand how they are built.

إنه لمن دواعي سروري دائماً أن أسمع مثل هذه الأشياء من مشاهدينا!

You are welcome!

Hello! If I understand you right, you need to follow these steps: 1) List the URLs or IDs of the sheets you want to query in a separate column 2) create a formula with INDIRECT 3) construct the QUERY formula In other words, to query a specific range in a sheet dynamically, you'll need to combine QUERY with INDIRECT. Here’s a simplified example: =QUERY(INDIRECT("'" & A2 & "'!A1:B10"), "SELECT A, B WHERE B > 10")

@@coupleracademy Thanks so much for the reply!!! Maybe if I give a bit more info, it could clarify my situation. So, my situation is such that I have a running list of reports for a calendar year. By the end of the year, there are 400 or so reports. Within the reports, there is an equipment2 sheet that I am trying to query with a range from B4:E28, where the results can be either 1 row, or all 25, depending on how much equipment was used for that given incident (potentially no rows, if no equipment was used). I have a script that imports all the URLs from the reports into a tab/sheet that is called ReportURLs, in a master tracking worksheet. Column D houses the URLs. I'm looking to query ReportURLs!D2:D dynamically to pull out all the data from each of the equipment2 tabs/sheets in each of the reports. Will your suggested Query/Indirect solution work for this? Thanks!

@@markuswinter-cdps3008 To be honest, it's hard to provide you with a working solution this way but at least I'll try to :) So: 1) go to your ReportURLs sheet and in column E (next to your URLs in column D), enter the following formula in E2: =IF(D2="", "", IMPORTRANGE(D2, "equipment2!B4:E28")) 2) Create a new sheet, let's call it CombinedData and in cell A1 enter the following formula: =QUERY(ARRAYFORMULA(FLATTEN(ReportURLs!E2:E)), "SELECT * WHERE Col1 IS NOT NULL", 0)

Sure! Just create a free Coupler.io account and create a new importer with Salesforce as a data source and Looker Studio as a destination. The setup process is pretty intuitive, but you can book a demo with our specialist to guide you through l.rw.rw/book-a-demo-yt

Do you see any specific error message?

@@coupleracademy no it says "you need permission, learn more" I am the owner of all of the files, but maybe because the google drive is managed by my employer?

@@coupleracademy do you have an email or support number to help? Call Google, I guess?

@@WorkoutNerd3221 Google typically does not offer live chat support for general inquiries or customer support issues. However, if you are a Google Workspace (formerly G Suite) customers, there is an option for live chat support available through the Google Admin Console.

Hope everything's clear now but if not, check out other Google Sheets tutorials on our channel, we explain different Google Sheets functions and functionalities in detail. Take care!

thanks! :)

You're very welcome!

Hi! Real-time no but you can have data refreshed as often as every 15 minutes.

Hi! It looks like there might be a syntax issue when using multiple LABEL statements with calculated columns. Ensure you have proper spacing and syntax. Here’s a corrected example: =QUERY(Invoices_Extracted_on_2024.07.21!A1:L, "SELECT Col"&XMATCH("INVOICE_ID",header)&", Col"&XMATCH("Date",header)&", Col"&XMATCH("Invoice#",header)&", Col"&XMATCH("Customer Name",header)&", Col"&XMATCH("Invoice Status",header)&", Col"&XMATCH("Due Date",header)&", Col"&XMATCH("Due Days",header)&", Col"&XMATCH("Sub Total",header)&", Col"&XMATCH("Invoice Amount",header)&" - Col"&XMATCH("Sub Total",header)&", Col"&XMATCH("Invoice Amount",header)&", Col"&XMATCH("Balance",header)&" - Col"&XMATCH("Invoice Amount",header)&", Col"&XMATCH("Balance",header)&", Col"&XMATCH("Adjustment",header)&", Col"&XMATCH("Created By",header)&" LABEL Col"&XMATCH("Invoice Amount",header)&" - Col"&XMATCH("Sub Total",header)&" 'Discount', Col"&XMATCH("Balance",header)&" - Col"&XMATCH("Invoice Amount",header)&" 'Paid sum'") The key is to ensure each LABEL is correctly associated with its column, and there’s proper spacing and punctuation.

You're welcome!

Hi! Thank you so much and glad you enjoyed our video. We don't have anything on IMPORTHTML planned at the moment but we'll definitely note your request and will discuss it with the team. Thanks again!

Thanks! Noted your request, we'll consider it when discussing future videos.

Noted, thanks for your feedback, we'll aim to improve in the future videos.

Yup, pretty neat! Thanks a lot :)

Hi! To do that, you can use the FILTER and SORT functions. Here’s a quick example: - Identify the columns: Use FILTER to get all relevant rows. - Sort by date: Use SORT to order by the date column, with the most recent first. - Extract the latest: Use INDEX to get the most recent entry. Example: =INDEX(SORT(FILTER(Sheet1!A:C, Sheet1!A:A = "YourValue"), 3, FALSE), 1, 1) This gets the most recent value from columns A to C where column A matches "YourValue" and sorts by the third column (date).

Hi! You can use ' ' (single quotes) for empty columns or NULL for null columns. For example: =QUERY(A:D, "SELECT A, '', B, NULL, C")

Thanks for the helpful tip! Regional settings can cause issues with separators. Switching from commas to semicolons is a great solution for those experiencing errors in QUERY syntax.

Welcome on board!

Thanks for your question! To create a daily projections vs actual dashboard with a filter view of the funnel: - Set up columns for dates, projections, and actual values. - Use SUMIF or QUERY formulas to aggregate daily data. - Create charts for projections vs actuals. - Add filter views via Data > Filter Views > Create New Filter View. - For a funnel chart, organize data by stages and use a bar chart.

How can I aggregate daily daily data? And also, what will be the best chart to use in this situation?

For daily aggregations, it's best to use either SUMIF or QUERY formulas. Here some examples: =SUMIF(A:A, "2024-01-01", B:B) =QUERY(A:B, "SELECT A, SUM(B) GROUP BY A") And as for the chart, i think line chart will work perfectly fine for the job :)

@@coupleracademy thank you so much!!

awesome to hear! :)

Hi! If your toolbox section isn't highlighted, it might be due to a couple of reasons: - Edit Mode: Make sure you're in Edit mode. You can switch to Edit mode by clicking the "Edit" button at the top right of your screen. - Permissions: Ensure you have the necessary permissions to edit the report. If it's a shared report, you might need edit access. - Browser Issues: Sometimes, a quick refresh or trying a different browser can help resolve display issues.

Hey there! Thanks for dropping by! 😊 It looks like you might have left your recipes in the Necromancy Area. That's quite an interesting place! If you need any help with Looker Studio, feel free to ask. We're here to help with data magic, not just necromancy! 😄

Hi! While Google Sheets doesn't have a built-in feature to link directly to a folder and automatically import data from all files in that folder, you can achieve this using Google Apps Script. I came across the following code that should do the job but haven't tested it, check if it works for you: function importFromFolder() { var folder = DriveApp.getFolderById('YOUR_FOLDER_ID'); var files = folder.getFiles(); var masterSheet = SpreadsheetApp.openById('YOUR_MASTER_SHEET_ID').getActiveSheet(); while (files.hasNext()) { var file = files.next(); var sheet = SpreadsheetApp.open(file).getSheets()[0]; var data = sheet.getDataRange().getValues(); masterSheet.getRange(masterSheet.getLastRow() + 1, 1, data.length, data[0].length).setValues(data); } }

@@coupleracademy Thank you

Hi! You can use the "Custom number format" option from the Format -> Number menu to decide on the number of decimal places.

Hi! How would you like to format it? You'll find plenty of options in the 'Format' menu, such as changing the currency symbol, number of decimals, different alignment within cell, etc

So happy to hear that. Thanks!

Hi! You'll want to use the following syntax: =GOOGLEFINANCE("ticker", "close", DATE(year, month, day)). For example: =GOOGLEFINANCE("AAPL", "close", DATE(2023, 3, 15)) If the stock market was closed on the chosen day, the function will return the close price for the next working day. If you have any other questions, feel free to ask :)

Have no fear! Have worked it out using INDEX, SUBSTITUTE, SPLIT and PROPER! Happy dayzzzzz

Hey there! 👋 Thanks for your question! Yes, you can definitely achieve this with a combination of Google Sheets API, WordPress, and n8n. Here’s a basic outline of how you can do it: 1. Google Sheets API: Use it to fetch your product catalog data. 2. n8n Workflow: Set up n8n to automate the process. You can create a workflow that: - Fetches data from Google Sheets. - Identifies and replaces keywords in the description column. - Updates images accordingly. 3. WordPress Integration: Use WordPress API to update the product details on your website with the modified data from n8n. By setting up this workflow, you can automate the process of replacing keywords and updating images in your product descriptions.

glad you think that!