Charting spreadsheet data conditionally

0
votes

I have data that's a bit complex. I need to be able to filter the results to a group of rows based on the value in a column called Cohorts. The source data looks like this: Chart data source

I need to be able to group the data by Program and then show only rows matching either Cohort "C1" or "C2", based on user selection. In Google sheets, I can add a Data Validation field and use that for Vlookups, but this is for filtering of the charts. Ultimately, I'd want to do this in something like Google Data Studio, but I'm just trying to understand how to create a method to dynamically select the correct data here in a simple spreadsheet/chart for now.

Update:
I can manually create a summary of the data in a new table and produce the following chart, but the goal, if possible, is to do all of this dynamically: Summary Chart

Thanks in advance for any advice.

1
google-sheetschartsgoogle-visualization
If you could share a sample sheet, where you show how you manually created the tables to produce the graphs you want, i think i could figure out how to dynamically recreate those tables. - MattKing
Please elaborate on the user experience of creating thee chart that you want. - Aerials
Sure, here's a sample worksheet with the original data, then two examples with a SumIfs() functions that have two conditions to narrow down the values. I probably need to figure out how to show data for both Cohorts in a single chart, so I really appreciate any advice you can share: docs.google.com/spreadsheets/d/… - Jav

1 Answers

1
votes

I have something that may give you some ideas.

I tried it with a slicer, but had a bit of trouble with the structure of your data.

In the end, I preferred using a query to filter the data for the chart, with the user able to easily modify the query from pull down options.

See my sample sheet here: https://docs.google.com/spreadsheets/d/1asklE1zkXlwbhavI8ry9tEjapwhRl6rcpbqZagDZH2E/edit?usp=sharing

The query formula is:

=query(A5:G20,"select A,B,sum(C),sum(D),sum(E),sum(F), SUM(G) where A <> '' " & L1 & L2 & " group by A,B label sum(C) 'Process1', sum(D) 'Process2', sum(E) 'Process3', sum(F) 'Process4', sum(G) 'Process5' ",1)

where L1 and L2 are modified based on a user selection. L2 could be equal to " and B contains 'C1' " for example.

I'm not very knowledgable about structuring data, so don't feel this is a final answer, but let me know if it helps.