Geckoboard’s Google Sheets’ integration is designed for displaying data, rather than calculating it. This means all calculations and aggregations in your spreadsheet need to be completed outside of Geckoboard.
That said, we've prepared a spreadsheet with ready-made calculations for you to copy and make your own.
Calculate totals by date units
In our experience, everyday spreadsheets tend to include – among other things – three columns containing dates, numbers and strings. Strings can represent anything from a sold item category to the salesperson responsible for selling it.
We'll use data from this example spreadsheet containing ready-made calculations. You can make a copy of this spreadsheet and use ImportRange to add your own sales data, or use your own.
Video tutorial: Use ImportRange to add your own sales data
In our example spreadsheet we've included query functions to generate totals for several date units. To learn more about the query function in Google Sheets, read this guide.
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 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.
