pivot with sum

Follow

Benjamin Runnels

• December 07, 2015 20:21

I'm trying to pivot if a column is true and I want a sum of all the count where that column is true. Example:

year month group members na_group asia_group
2015 12 US 350 true false
2015 12 MEX 150 true false
2015 12 APAC 250 false true
2015 12 JPN 450 false true
2015 11 US 300 true false
2015 11 MEX 100 true false
2015 11 APAC 200 false true
2015 11 JPN 400 false true

So I want to pivot and have columns
year, month, na_group_total_members, asia_group_total_members, global_total_members

2015, 12, 500, 700, 1200
2015, 11, 400, 600, 1000

0

• Official comment

  

  Joel Stewart

  • December 07, 2015 20:47

  I would accomplish this particular use case using a GROUPBY and GROUPSUM function with a conditional statement to sum the members value if na_group is true or 0 if it is false:

  GROUPBY(#MySheet!year)
  GROUPBY(#MySheet!month)
  GROUPSUM(IF(#MySheet!na_group;#MySheet!members;0))
  GROUPSUM(IF(#MySheet!asia_group;#MySheet!members;0))

  For the global_total_members, I would just add the partial columns together: i.e. #C + #D

  Comment actions Permalink
• 

  Benjamin Runnels

  • December 07, 2015 20:55

  Yes, that worked. Thank You! I know I was trying that as well but I think I keep messing up the syntax with commas instead of semicolons.

  0

  Comment actions Permalink

Please sign in to leave a comment.