CODEWITHSUNDEEP
google-sheetschartsgoogle-visualization
Jav
Jav

Reputation: 573

Charting spreadsheet data conditionally

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.

Upvotes: 0

Views: 4105

Answers (1)

kirkg13
kirkg13

Reputation: 3010

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.

Upvotes: 2

Related Questions