Pivot Charts in google Sheets by counting non-numeric data? -

-

i have dataset i'd summarize in chart form. there 30 categories counts i'd display in bar chart 300+ responses. think pivot table best way this, when create pivot table , select multiple columns, each new column added gets entered sub-set of previous column. data looks following

id    country    age    thinga    thingb    thingc    thingd    thinge    thingf 1             5-9                thb                 thd                thf 2      fi        5-9     tha                                               thf 3      ga        5-9     tha                                               thf                          4             10-14                       thc 5             10-14              thb                                    thf                          6             15-18                      7      br        5-9     tha                                                                                              8             15-18                                  thd                thf                       9      fi        10-14   tha

so, i'd able create interactive chart showed counts of "thing" items; i'd able filter based upon demographic data (e.g., country, age). notice data non-numeric, have use counta see how many there in each category.

is there simple way display chart data summarizes counts , allow me filter based on different criteria?

the query can summarize data in form want. fact have "tha", "thb", etc, instead of "1" complicates matter, 1 can transform strings numeric data on fly.

assuming data you've shown in cells a1:i10, following formula summarize it: 

=query({b2:c10, arrayformula(if(len(d2:i10), 1, 0))}, "select col1, col2, count(col3), sum(col3), sum(col4), sum(col5), sum(col6), sum(col7) group col1, col2", 0)

explanation:

  • {b2:c10, arrayformula(if(len(d2:i10), 1, 0))} creates table first 2 columns b,c (country, age) , other 6 filled 1 or 0 depending on whether cells in d-i filled or not.
  • select col1, col2, count(col3), sum(col3), ... group col1, col2 selects country, age, total count of rows country-age combination, number of rows thinga country-age combination, etc.
  • the last argument, 0, indicates there no header rows in table passed query.

it's possible give labels columns returned query, using label: see query language documentation. 

label col1 'country', col2 'age', count(col3) 'total count', sum(col3) 'thinga count', ...

Comments

Post a Comment

Popular posts from this blog

wordpress - (T_ENDFOREACH) php error -

-
creating wordpress website , i'm getting following php error: parse error: syntax error, unexpected 'endforeach' (t_endforeach) in /home/soralmedia/public_html/wp-content/themes/soral media/woocommerce/myaccount/my-orders.php on line 98 here's code file: <?php /** * orders * * shows recent orders on account page. * * template can overridden copying yourtheme/woocommerce/myaccount/my-orders.php. * * however, on occasion woocommerce need update template files , (the theme developer). * need copy new files theme maintain compatibility. try this. * little possible, happen. when occurs version of template file will. * bumped , readme list important changes. * * @see http://docs.woothemes.com/document/template-structure/ * @author woothemes * @package woocommerce/templates * @version 2.5.0 */ if ( ! defined( 'abspath' ) ) { exit; } $my_orders_columns = apply_filters( 'woocommerce_my_account_my_orders_columns', array(
Read more

Export Excel workseet into txt file using vba - (text and numbers with formulas) -

-
i new vba have developed code use in excel move text/numbers txt file. issue have 3 functions need go same txt file. when run first function has set fs = createobject("scripting.filesystemobject") set = fs.createtextfile(module1.nys45uploadfilename, true) a.writeline (str) a.close. the other 2 functions had save. set fs = createobject("scripting.filesystemobject") set = fs.savetextfile(module1.nys45uploadfilename, true) a.writeline (str) a.close. the first function go txt file, other 2 error object doesn't support property or method. when changed 3 create last function goes txt file. can not figure out word use in order make other 2 functions follow txt file in order keyed. the entire code follows private sub addrecord_click() on error goto errhandler module1.nys45uploadfilename = application.getsaveasfilename(filefilter:="textfiles (*.txt), *.txt") if module1.nys45uploadfilename = "false" exit sub
Read more

Using django-mptt to get only the categories that have items -

-
sample structure: all uppercase: category mixed case: item root ├── books │   ├── fiction │   │   └── classics │   └── non-fiction ├── clothing └── electronics ├── laptops ├── phones │   ├── apple │   │   ├── iphone 6 │   │   ├── iphone 6 plus │   │   ├── iphone 6s │   │   └── iphone 6s plus │   ├── motorola │   │   ├── moto g4 │   │   ├── moto g4 play │   │   ├── moto g4 plus │   │   └── moto x │   └── samsung └── tablets └── apple i'm trying have index page show "electronics" category. "clothing" category shouldn't show since it's empty , "books" category shouldn't show either since, although has child categories, child categories don't have items. similarly, "electronics" page should show "phones" category. "laptops" category shouldn't show since it's empty , "tablets" category shouldn't show either since
Read more