The Spreadsheets data source is designed to display data rather than calculate it. This means any calculations and aggregations in your spreadsheet need to be completed before you bring the data into Geckoboard to visualize it.
In this guide we'll cover a number of ways you can use the Query function to make calculations or aggregations of your data within a Google Sheet. To learn more about the Query function in Google Sheets, we recommend this guide.
We'll use data from this example spreadsheet, which contains ready-made calculations. Make a copy of the sheet, then use ImportRange to add your own sales data, or feel free to follow along with your own sheet.
Use ImportRange to add your own sales data
Calculate totals by date units
In our experience, spreadsheets typically include three common columns: dates, numbers, and strings. Strings can represent anything from a sold item category to the salesperson responsible for selling it.
In our example spreadsheet, we've built Query functions to generate totals for several date units, which we'll explain below and provide an example query for. Here are the quick links to each section:
Today
This query sums the numbers in column B, where a date in column A equals today's date. It then generates a heading of 'Total Today'. The query is wrapped in an IFERROR function to provide a fallback when no dates match today's date.
=IFERROR(QUERY(A2:C,"select sum(B) where A = date '"&text(TODAY(),"yyyy-mm-dd")&"' LABEL sum (B) 'Total Today'"),"Total Today")
Today grouped by string
This query groups today's sales and uses a pivot clause to output the column titles.
=IFERROR(QUERY(A1:C,"select C, sum(B) where A = date '"&text(TODAY(),"yyyy-mm-dd")&"' group by C pivot A label C 'Total On'"),"Total On")
Visualize today's sales by teammate
Use a leaderboard to track today's total sales by teammate.
Yesterday
This query sums the numbers in column B where a date in column A equals today's date minus 1 day. It then generates a heading of 'Total Yesterday'. The query is wrapped in an IFERROR function to provide a fallback when no dates match yesterday's date.
=IFERROR(QUERY(A2:C,"select sum(B) where A = date '"&text(TODAY()-1,"yyyy-mm-dd")&"' LABEL sum (B) 'Total Yesterday'"),"Total Yesterday")
Visualize today's and yesterday's sales
Use the comparison visualizations to compare today's and yesterday's sales and work towards a daily target.
Past 7 days
This query sums the numbers in column B where a date in column A is less than or equal to today's date and greater than today's date minus 7 days. It then generates a heading of 'Total Past 7 Days'. The query is wrapped in an IFERROR function to provide a fallback when no dates match.
=IFERROR(QUERY(A2:C,"select sum(B) where A <= date '"&text(TODAY(),"yyyy-mm-dd")&"' and A > date '"&text(TODAY()-7,"yyyy-mm-dd")&"' LABEL sum (B) 'Total Past 7 Days'"),"Total Past 7 Days")
Previous 7 days
This query sums the numbers in column B where a date in column A is less than or equal to today's date 7 days ago and greater than today's date minus 14 days. It then generates a heading of 'Total Previous 7 days'. The query is wrapped in an IFERROR function to provide a fallback when no dates match.
=IFERROR(QUERY(A2:C,"select sum(B) where A <= date '"&text(TODAY()-7,"yyyy-mm-dd")&"' and A > date '"&text(TODAY()-14,"yyyy-mm-dd")&"' LABEL sum (B) 'Total Previous 7 Days'"),"Total Previous 7 Days")
Visualize sales from past 7 days
Use the comparison visualizations to compare sales from the past 7 days with the previous 7 days, and work towards a target for this period.
So far this week
This query sums the numbers in column B where a date in column A matches today's date and the week it falls in. It then generates a heading of 'Total This Week'. The query is wrapped in an IFERROR function to provide a fallback when no dates match.
=IFERROR(QUERY(A2:C,"select sum(B) where A > date '"&text(floor(today() - 1, 7),"yyyy-mm-dd")&"' LABEL sum (B) 'Total This Week'"),"Total This Week")
So far last week
This query sums the numbers in column B where a date in column A matches today's date and the previous week it falls in. It then generates a heading of 'Total So Far Last Week'. The query is wrapped in an IFERROR function to provide a fallback when no dates match.
=IFERROR(QUERY(A2:C,"select sum(B) where A > date '"&text(floor(today() - 8, 15),"yyyy-mm-dd")&"' LABEL sum (B) 'Total So Far Last Week'"),"Total So Far Last Week")
Visualize sales for the week so far
Use the comparison visualizations to compare sales for the week so far with the previous week, and work towards a target for this period.
This month
This query sums the numbers in column B where a date in column A matches today's date in its month and year. It then generates a heading of 'Total This Month'. The query is wrapped in an IFERROR function to provide a fallback when no dates match.
=IFERROR(query(A1:C,"select SUM(B) where month(A)=month(date'"&text(today(),"YYYY-MM-DD")&"') and Year(A)=Year(date'"&text(today(),"YYYY-MM-DD")&"') label SUM(B) 'Total This Month'"),"Total This Month")
Last month
This query sums the numbers in column B where a date in column A matches today's date in its month minus 1 month, and also matches the year. It then generates a heading of 'This month'. The query is wrapped in an IFERROR function to provide a fallback when no dates match.
=IFERROR(QUERY(A1:C,"select SUM(B) where month(A)=month(date'"&text(today(),"YYYY-MM-DD")&"')-1 and Year(A)=Year(date'"&text(today(),"YYYY-MM-DD")&"') label SUM(B) 'Total Last Month'"),"Total Last Month")
Visualize sales for the month so far
Use the comparison visualizations to work towards a target for this month.
Months grouped by string
This query uses an array formula to output the sum numbers in column B grouped by the month number.
=IFERROR(QUERY( ARRAYFORMULA({TEXT(A:A,"01-MM-YYYY"),B:B}), "select Col1,sum(Col2) where Col2 is not null group by Col1 " ),"Date")
Visualize sales totals by month
Use a column chart to track the total sales for each month.
