Create a Chart for Membership Data

Question

Was wondering how to best set up data to create charts for memberships.

I want to create a chart that compares # of New Members vs. # of Cancelled members. In the data, I have one columns that contains their start date (for subscribing) and one column that contains their cancellation date.

I'm not sure how to best set this up so that I can create a line or bar chart.

Answer 1

Assuming your data is in columns A1:C and looks like this:

Name	Started	Cancelled
Jayne	4/10/2023	7/21/2023
Melanie	4/10/2023	4/27/2023
Victoria	4/11/2023
Geri	4/12/2023
Emma	4/29/2023
John	5/16/2023
Paul	4/10/2023
George	4/27/2023	7/21/2023
Ringo	4/29/2023

...you can unpivot the data so it looks like this:

Name	Status	Date
Jayne	Started	4/10/2023
Jayne	Cancelled	7/21/2023
Melanie	Started	4/10/2023
Melanie	Cancelled	4/27/2023
Victoria	Started	4/11/2023
Geri	Started	4/12/2023
Emma	Started	4/29/2023
John	Started	5/16/2023
Paul	Started	4/10/2023
George	Started	4/27/2023
George	Cancelled	7/21/2023
Ringo	Started	4/29/2023

...and then aggregate and re-pivot the data so it looks like this:

By date:

Date	Cancelled	Started
4/10/2023		3
4/11/2023		1
4/12/2023		1
4/27/2023	1	1
4/29/2023		2
5/16/2023		1
7/21/2023	2

By week:

Date	Cancelled	Started
2023-w15		5
2023-w17	1	3
2023-w20		1
2023-w29	2

By month:

Date	Cancelled	Started
2023-04	1	8
2023-05		1
2023-07	2

By year:

Date	Cancelled	Started
2023	3	9

Here's one way to unpivot the data:

=let( 

  table, A1:C, 
  numColsToRepeat, 1, 

  numColsToSqueeze, columns(table) - numColsToRepeat, 
  headers, { offset(table, 0, 0, 1, numColsToRepeat), "Status", "Date" }, 
  categories, offset(table, 0, 0, 1), 
  colsToRepeat, offset(table, 1, 0, rows(table) - 1, numColsToRepeat), 
  colsToSqueeze, offset(table, 1, numColsToRepeat, rows(table) - 1, numColsToSqueeze), 
  reduce( 
    headers, colsToSqueeze, 
    lambda( 
      result, cell, 
      if( 
        cell = "", 
        result, 
        { 
          result; 
          { 
            index(colsToRepeat, row(cell) - row(colsToSqueeze) + 1), 
            index(categories, 1, column(cell) - column(table) + 1), 
            cell 
          } 
        } 
      ) 
    ) 
  ) 
)

The formula assumes that the data is in columns A1:C.

Here's one example of how to aggregate and re-pivot the unpivoted data:

=let( 
  data, filter(E1:G, len(E1:E)), 
  aggregation, 
    "select Col3, count(Col1) 
     where Col3 is not null 
     group by Col3 
     pivot Col2", 
  names, choosecols(data, 1), 
  statuses, choosecols(data, 2), 
  dates, choosecols(data, 3), 
  twoDigit_, lambda(n, text(n, "00")), 
  day_, lambda(d, to_date(d)), 
  week_, lambda(d, iferror(year(d) & "-w" & twoDigit_(weeknum(d)), d)), 
  month_, lambda(d, text(d, "yyyy-MM")), 
  year_, lambda(d, iferror(year(d), d)), 
  getData_, lambda(period_, { names, statuses, map(dates, period_) } ), 
  report, query(getData_(month_), aggregation, 1 ), 
  report 
)

The formula assumes that the unpivoted data is in columns E1:G.

To change between daily, weekly, monthly and yearly reporting, replace the month_ in getData_(month_) with day_, week_, month_ or year_.

To create a chart, select the resulting table and choose Insert > Chart.