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.