Variable Default Value - Control Field

[Steve Ouellette]

Hello,

I have two Drop-Down List control fields (I'll call them Field A and Field B) which are associated with different data sets in a Looker Studio report. Both contain a list of numbers from 1 to 15. 

When a user selects a value for Field A, I would like the value of Field B  to automatically default to one more than Field A. For example, let's say the user selects 7 in Field A, is it possible for Field B to automatically be set to the number 8?

I hope this request is clear. Any help is much appreciated.

Steve

___

Steve Ouellette, CETL

Director of Technology, Learning, and Innovation

Westwood Public Schools

220 Nahatan St.

Westwood, MA  02090

781-326-7500 x3364

_____________________________________

If you need this information translated, please copy and paste it into Google Translate.  The link to Google translate is http://translate.google.com/

Spanish

Si necesita que se le traduzca esta información, por favor, copie y pegue en Google Translate. El enlace de Google Translate es http://translate.google.com/

 Chinese

如果您需要翻译此信息，请复制并将其粘贴到Google翻译中。指向Google翻译的链接是http://translate.google.com/

 Arabic

إذا كنت بحاجة إلى ترجمة هذه المعلومات فالرجاء نسخها ولصقها في ترجمة Google. الرابط إلى ترجمة Google هو    http://translate.google.com/

--
Please be advised that the Massachusetts Attorney General has determined that email is a public record.

[Anthony Claypool]

I'm sure there's someone here who knows more than I, but here's my stab.

I don't think that would be possible.  The filter control references values in the underlying table and unless all rows with value 7 in field A also have value 8 in field B I don't think it would work.  You could always create an alternative field B using a calculated field (CASE WHEN A = 7 THEN 8, etc.), but I'm not sure what good that might do.  Have you any screenshots or maybe more details about the problem you're trying to solve?  OR anyone wanna tell me how wrong I am?! 🤣

--
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 on the web visit https://groups.google.com/d/msgid/googleschool-datastudio/CAFc8rncUKdxapFY1Kh%2BP4UewoAk%3DkWe9_UHLYw6haeuZgnj2SA%40mail.gmail.com.

--

Anthony Claypool Director of Data Systems & Analytics, Distinctive Schools (312)332-4998   www.distinctiveschools.org acla...@distinctiveschools.org 910 W. Van Buren Street, Suite 315, Chicago , IL 60607 IMPORTANT: The contents of this email and any attachments are confidential. They are intended for the named recipient(s) only. If you have received this email by mistake, please notify the sender immediately and do not disclose the contents to anyone or make copies thereof.

[Steve Ouellette]

Thanks for getting back to me Anthony. Here's a bit more context:

I am building a salary lookup tool for staff as we are in the process of negotiations. Here's a screenshot of the tool:

Each year (FY23, FY24, FY25) draws from a different tab of the same Google Sheet. When someone selects their 'current step' (first yellow box), the year 2 and year 3 steps are simply one more than the previous step (with the caveat that 15 is the highest step). It would be helpful if someone selected 5 for current step, that the Year2 and Year 3 steps would automatically filter to 6 and 7, respectively. Unfortunately, I don't see a way to update the default value for those filters based on the value of 'current step'. 

To view this discussion on the web visit https://groups.google.com/d/msgid/googleschool-datastudio/CA%2BD3xe%2Bf%3D99Z3r4nse%3DHWFnnjqpqCtbAo0vn7HctN5A2CnrqBQ%40mail.gmail.com.

[Laura Tilton *]

Hi Steve,

Assuming you don't want to blend or VLOOKUP the data into a single source...

My initial thought is to use parameters across all 3 sources to select "Current Step" which would then control what values are included in the other drop-downs.

Here's a quick example (plus data source) and how I did it. (Numbers may not make sense, and my max year is 7, but you'll get the idea.)

• Set up a whole number parameter (1 to 15; don't use range) in each data source with the same parameter ID. I used the parameter name and ID of current_year_select.
• Create a calculated field in each data source called “Include” that will return 0 or 1 
• - In Current Year Source: IF ( current_year_select  = Year, 1, 0) 
• - In Year 2 Source: IF( current_year_select  = Year-1, 1, 0)
• - In Year 3 Source: IF( current_year_select  = Year-2, 1, 0)
• Then, set up filters on each of the tables to only show where Include=1 on each data source.
  

This is slightly different than your request, because Years 2 and 3 aren't really "defaulting" to the proper values, they are being filtered to the proper values, but I think the functionality is the same? You'd also need to build something in for what happens when the year is the max year.

Hope that's helpful, let me know if you have questions!

Laura

To view this discussion on the web visit https://groups.google.com/d/msgid/googleschool-datastudio/CAFc8rnf3gq1H4QEoj8GdT%3DmbtmE%3D8kP9ALgYFmqx%3DPV7txV2rg%40mail.gmail.com.

--

Laura Tilton

Education Data Professional

Twitter: @tiltondata

[Steve Ouellette]

Thanks very much Laura. I'll dig into this later today but it looks like your example does what I'm looking for.

Much appreciated!

Steve

To view this discussion on the web visit https://groups.google.com/d/msgid/googleschool-datastudio/CAO_um7tTUSgLBsGtntBF67Z%3DQvm50FWrDCdk4eivSmvS6SZDAw%40mail.gmail.com.

[Harpreet Gill]

you could use the filter function for Year 2 Step cell and bring in the information based on what's in Current Step.