Date(year) control

[Maynard, Ben P - Office of Education Technology]

Morning!

Something I’ve been needing to solve for a while now so thought I’d ask the collective experts to see how you may be approaching this.

I need users to have the ability to select a ‘year’ on a report to view the same charts but reflect the year they chose. I’m getting a bit tired of building a report to connect the new data to each year.

 

Data sources connected to Sheets.

Data sources have the same dimensions (for the most part), including a date.

 

Should I be leaning toward a blended data source for this? Or is there a way I’m not thinking about?

Anyone have an example?

Is there info I need to provide so you can offer a suggestion/solution? Happy to do it.

Thanks all!

 

Ben Maynard

Digital Learning Coach

School Digital Readiness Services

Office of Education Technology

 

Phone| (502) 892.6328

Email| ben.m...@education.ky.gov

300 Sower Blvd. – 4th floor

Frankfort, KY 40601

@KyDeptofEd

@benmaynard31

 

This email may contain confidential data or information and is intended solely for the use of the individual or entity to whom it is addressed. If you are not the named addressee you should not disseminate, distribute, or copy this e-mail, and you are requested to notify the sender immediately.

 

[Chris Smith]

Ben,

Hey there. I do this quite often with annual external assessments. As you mentioned, you have the data in spreadsheets. My typical route of attack is to bring the data into a single data source and stack each assessment. You can do this through some formulas so that the data is updated dynamically. (Happy to share if it is the right method for you.) Once you have the data stacked, you can then connect that single tab in your Sheet to Looker and visualize. You can then create a control field with the year being the dimension for the dropdown. 

Happy to guide you through it, if needed.

Best,

Chris

[Laura Tilton]

Hi Ben, yes! If you've got the same or similar fields for each year, you can stack them all together in Sheets using IMPORTRANGE to create a unified data source. (So as not to reinvent the wheel, here's a thread where this is discussed, wrapping everything in QUERY to only select populated rows.) You'd then use the Date field from the new combined source to create your Year filter control in your Looker Studio report. 

Just a note to clarify since you mentioned blending. Blending two data sources is generally about bringing different fields together based on a common key (a JOIN in SQL). What you want is more like the UNION in SQL (Looker Studio doesn't really have a feature for this)...so another option is to connect your sheets as tables in BigQuery, then create a view using UNION that stacks it all together, and use that view as the source for your report. But combining in Sheets is likely the simplest route.

Best,

Laura

--
You received this message because you are subscribed to the Google Groups "Data Studio in Education" group.
To unsubscribe from this group and stop receiving emails from it, send an email to googleschool-data...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/googleschool-datastudio/DM4PR02MB893642B69605723873FF9559D72DA%40DM4PR02MB8936.namprd02.prod.outlook.com.

[Maynard, Ben P - Office of Education Technology]

Chris and Laura

Thank you for the suggestions and information, as well as the offer to help step me through it. I ‘should’ be able to pull off combining the Sheets but if I run into any issues I won’t hesitate to take you up on that offer.

Laura, the BigQuery suggestion is where I’d like to eventually move to on this. We have access to Looker Pro and BigQuery but BQ is currently collecting dust. So I may use this as a reason to get our office moving on utilizing it.

 

Ben Maynard

Digital Learning Coach

School Digital Readiness Services

Office of Education Technology

 

Phone| (502) 892.6328

Email| ben.m...@education.ky.gov

300 Sower Blvd. – 4th floor

Frankfort, KY 40601

@KyDeptofEd

@benmaynard31

 

This email may contain confidential data or information and is intended solely for the use of the individual or entity to whom it is addressed. If you are not the named addressee you should not disseminate, distribute, or copy this e-mail, and you are requested to notify the sender immediately.

 

 

From: 'Laura Tilton' via Data Studio in Education <googleschoo...@googlegroups.com>
Date: Thursday, August 7, 2025 at 7:02 AM
To: googleschoo...@googlegroups.com <googleschoo...@googlegroups.com>
Subject: Re: [Data Studio in Edu] Date(year) control

CAUTION:  This email originated from outside of the Kentucky Department of Education, Kentucky K-12 schools, or Kentucky State Government. Do not click links, open attachments or forward unless you recognize the sender and know the content is safe.

 

To view this discussion visit https://groups.google.com/d/msgid/googleschool-datastudio/CAN4-9217ohZ%2BHm3FjWrDb2WbDTA0ONyx8VDWbn5RycMBC6vV8w%40mail.gmail.com.