0

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.

1

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.