In our company, we utilize a grading system to evaluate the technical performance of our agents, assigning grades of C, B, A, and A+. The grade information for various dates is presented in the question table. We aim to assess the number of upgrades, downgrades, and instances of no change each month by comparing the last grade of agents in that month with their last grade from the previous month. For example, in month 1, the final grade of Agent-4 is C, which was A in the previous month, indicating a reduction in grade.
📌 Challenge Details and Links
Challenge Number: 35
Challenge Difficulty: ⭐⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of P & Down Grades! with Power Query
Power Query solution 1 for P & Down Grades!, proposed by Omid Motamedisedeh:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Month = Table.TransformColumns(Source, {"Date", each Number.Mod(Date.Month(_), 12)}),
Group = Table.Group(
Month,
{"Date", "Agent-id"},
{"Grade", each List.PositionOf({"C", "B", "A", "A+"}, List.Last(_[Grade]))}
),
#"Added Custom" = Table.AddColumn(
Group,
"Previous_grade",
each List.Last(
Table.SelectRows(Group, (x) => x[Date] < [Date] and x[#"Agent-id"] = _[#"Agent-id"])[Grade]
)
),
#"Added Custom1" = Table.AddColumn(
#"Added Custom",
"Custom",
each
if [Grade] > [Previous_grade] then
"Up Grade"
else if [Grade] < [Previous_grade] then
"Down Grade"
else
"No Change"
),
#"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Date] <> 0)),
#"Grouped Rows" = Table.Group(
#"Filtered Rows",
{"Date", "Custom"},
{{"Count", each Table.RowCount(_), Int64.Type}}
),
#"Pivoted Column" = Table.Pivot(
#"Grouped Rows",
List.Distinct(#"Grouped Rows"[Custom]),
"Custom",
"Count",
List.Sum
)
in
#"Pivoted Column"Power Query solution 2 for P & Down Grades!, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Val = List.Zip({{"A+", "A", "B", "C"}, List.Reverse({1..4})}),
Month = Table.AddColumn(Source, "Month", each Date.Month([Date])),
Sort = Table.Sort(Month,{{"Date", Order.Ascending}}),
Group = Table.Group(Sort, {"Agent-id"}, {{"A", each
let
a = Table.AddColumn(_, "Val", each List.ReplaceMatchingItems({[Grade]}, Val){0}),
b = Table.Combine(Table.Group(a, {"Month"}, {"B", each Table.FromRecords({Table.Last(_)})})[B]),
c = {null}&List.RemoveLastN(List.Transform({0..List.Count(b[Val])-1}, each if b[Val]{_+1}-b[Val]{_} > 0 then "Upgrade" else
if b[Val]{_+1}-b[Val]{_} < 0 then "Down-grade" else "No Change" )),
d = Table.SelectRows(Table.FromColumns(Table.ToColumns(b)&{c}, Table.ColumnNames(b)&{"C"}), each [C]<>null)[[Month],[C]]
in d}}),
Expand = Table.ExpandTableColumn(Group, "A", {"Month", "C"}, {"Month", "C"})[[Month], [C]],
Group2 = Table.Group(Expand, {"Month"}, {{"D", each
Table.Pivot(_, List.Sort(List.Distinct(Expand[C]), 1), "C", "Month", List.Count)}}),
Sol = Table.ExpandTableColumn(Group2, "D", Table.ColumnNames(Group2[D]{0}))
in
SolPower Query solution 3 for P & Down Grades!, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
A = Table.TransformColumnTypes(S,{{"Date", type date}}),
B = Table.AddColumn(A, "Y-M", each Date.ToText([Date], [Format="yyMM", Culture="en-EN"])),
C = Table.Group(B, {"Agent-id", "Y-M"}, {{"LastDate", each List.Max([Date]), type nullable date}}),
D = Table.NestedJoin(C,{"Agent-id","LastDate"},A,{"Agent-id","Date"},"Max"),
E = Table.ExpandTableColumn(D, "Max", {"Grade"}, {"Grade"}),
H = Table.AddColumn(E, "Score", each if [Grade] = "A+" then 4 else if [Grade] = "A" then 3 else if [Grade] = "B" then 2 else 1),
J = Table.Group(H, {"Agent-id"}, {{"T", each _, type table [#"Agent-id"=text, #"Y-M"=text, MaxD=nullable date, MinD=nullable date, Max.Grade=text, Min.Grade=text, MaxS=number, MinS=number]}}),
Fu=(X)=>
let
F1 = Table.Sort(X,{{"Y-M", Order.Ascending}}),
F2 = Table.AddIndexColumn(F1, "I", 0, 1, Int64.Type),
F3 = Table.AddColumn(F2, "R", each try [Score]-F2[Score]{[I]-1} otherwise null)
in
F3,
Solving the challenge of P & Down Grades! with Excel
Excel solution 1 for P & Down Grades!, proposed by Bo Rydobon 🇹🇭:
=LET(
m,
MOD(
MONTH(
B3:B37
),
12
)+10,
a,
C3:C37,
u,
UNIQUE(
FILTER(
HSTACK(
m-10,
a&m-{0,
1}
),
m>10
)
),
n,
TAKE(
u,
,
1
), p,
DROP(
PIVOTBY(
n,
SIGN(
MMULT(
XLOOKUP(
DROP(
u,
,
1
),
a&m,
XMATCH(
D3:D37,
{"C",
"B",
"A",
"A+"}
),
,
{0,
-1},
-1
),
{-1;1}
)
),
n,
ROWS,
,
0,
,
0
),
1
), VSTACK(
{"Month",
"Upgrade",
"No Change",
"Down-grade"},
N(
p
)
)
)Excel solution 2 for P & Down Grades!, proposed by 🇰🇷 Taeyong Shin:
=LET(
dt,
B3:B37,
id,
C3:C37,
gd,
XMATCH(
D3:D37,
{"A+";"A";"B";"C"}
),
m,
MONTH(
dt
), t,
MAP(EOMONTH(
+dt,
-1
),
dt,
id,
gd,
LAMBDA(a,
b,
c,
d, LET(
x,
XLOOKUP(
a,
IFS(
id = c,
dt
),
gd,
0,
-1
), REPT(IFS(
x > d,
"Upgrade",
x = d,
"No Change",
TRUE,
"Down-grade"
), MAX((MONTH(
b
) & c = m & id) * dt) = b)
)
)), DROP(
PIVOTBY(
m,
t,
t,
ROWS,
,
0,
,
0,
-1,
m < 12
),
,
-1
)
)Excel solution 3 for P & Down Grades!, proposed by محمد حلمي:
=REDUCE(
G2:J2,
SEQUENCE(
4
),
LAMBDA(
a,
d,
LET(
p,
MONTH(
A3:A37
),
dd,
UNIQUE(
p
),
ss,
INDEX(
DROP(
dd,
1
),
d
),
xx,
FILTER(
A3:C37,
p=ss
),
rr,
INDEX(
xx,
,
2
),
qq,
DROP(
xx,
,
2
),
w,
FILTER(
B3:C37,
p=INDEX(
DROP(
dd,
-1
),
d
)
),
n,
UNIQUE(
rr
),
e,
XLOOKUP(
n,
TAKE(
w,
,
1
),
DROP(
w,
,
1
),
0,
,
-1
),
r,
XLOOKUP(
n,
rr,
qq,
0,
,
-1
),
I,
SUM(
N(
r=e
)
),
v,
SUM(
N(
RIGHT(
e
)Excel solution 4 for P & Down Grades!, proposed by Oscar Mendez Roca Farell:
=REDUCE(
HSTACK(
"Month",
"No "&{"Upgrade",
"Change",
"Down-grade"}
),
{12,
1,
2,
3},
LAMBDA(
i,
x,
LET(
j,
MOD(
x+1,
12
),
B,
MONTH(
B3:B37
),
U,
UNIQUE(
C3:C37
),
F,
LAMBDA(
y,
XMATCH(
XLOOKUP(
y&U,
B&C3:C37,
D3:D37,
,
,
-1
),
{"A+",
"A",
"B",
"C"}
)
),
M,
TOCOL(
F(
j
)-IFNA(
F(
x
),
4
),
2
),
VSTACK(
i,
HSTACK(
j,
SUM(
N(
M<0
)
),
SUM(
N(
M=0
)
),
SUM(
N(
M>0
)
)
)
)
)
)
)Excel solution 5 for P & Down Grades!, proposed by Julian Poeltl:
=LET(T,
B2:D37,
TT,
DROP(
T,
1
),
M,
TAKE(
TT,
,
1
),
MM,
MONTH(
M
),
MMM,
IF(
MM=12,
1,
MM+1
),
AG,
CHOOSECOLS(
TT,
2
),
AGU,
UNIQUE(
AG
),
G,
SWITCH(
TAKE(
TT,
,
-1
),
"A+",
1,
"A",
2,
"B",
3,
"C",
4
),
GCME,
LAMBDA(A,
ID,
TAKE(FILTER(G,
(MMM=A)*(AG=ID)),
-1)),
GPM,
LAMBDA(A,
ID,
TAKE(FILTER(G,
(MMM<=A-1)*(AG=ID)),
-1)),
R,
LAMBDA(
M,
MAP(
AGU,
LAMBDA(
A,
GCME(
M,
A
)-GPM(
M,
A
)
)
)
),
RR,
LAMBDA(
B,
CONCAT(
B-1,
SUM(
--IFERROR(
R(
B
)<0,
0
)
),
SUM(
--IFERROR(
R(
B
)=0,
0
)
),
SUM(
--IFERROR(
R(
B
)>0,
0
)
)
)
),
NC,
MAP(
SEQUENCE(
4,
,
2
),
LAMBDA(
A,
RR(
A
)
)
),
VSTACK(
HSTACK(
"Month",
"No Upgrade",
"No Change",
"No Down-Grade"
),
MID(
NC,
SEQUENCE(
,
4
),
1
)
))Excel solution 6 for P & Down Grades!, proposed by Kris Jaganah:
=LET(a,
B3:B37,
b,
GROUPBY(
HSTACK(
MOD(
MONTH(
a
),
12
),
C3:C37
),
SWITCH(
D3:D37,
"A+",
4,
"A",
3,
"B",
2,
"C",
1
),
CONCAT,
0,
0
),
c,
TAKE(
b,
,
1
),
d,
CHOOSECOLS(
b,
2
),
e,
TAKE(
b,
,
-1
),
f,
RIGHT(
e
)-RIGHT(MAP(c,
d,
LAMBDA(x,
y,
FILTER(e,
(c=x-1)*(d=y),
0)))),
g,
IFS(
f>0,
2,
f<0,
0,
1,
1
),
h,
DROP(
UNIQUE(
c
),
1
),
VSTACK({"Month",
"Upgrade",
"No Change",
"Down-grade"},
HSTACK(h,
WRAPROWS(MAP(TOCOL(
h&{2,
1,
0}
),
LAMBDA(x,
SUM((c=--LEFT(
x
))*(g=--RIGHT(
x
))))),
3))))Excel solution 7 for P & Down Grades!, proposed by John Jairo Vergara Domínguez:
=LET(
d,
B3:B37,
i,
C3:C37,
a,
GROUPBY(
HSTACK(
MOD(
MONTH(
d
),
12
),
i
),
d,
MAX,
,
0
),
j,
INDEX(
a,
,
2
),
m,
TAKE(
a,
,
1
),
p,
XMATCH(
XLOOKUP(
j&INDEX(
a,
,
3
),
i&d,
D3:D37
),
{"C";"B";"A";"A+"}
),
b,
SIGN(
IFNA(
XLOOKUP(
j&m-1,
j&m,
p
),
p-1
)-p
),
VSTACK(
{"Month",
"Upgrade",
"No Change",
"Down-grade"},
N(
DROP(
PIVOTBY(
m,
b,
b,
ROWS,
,
0,
,
0,
,
m
),
1
)
)
)
)Excel solution 8 for P & Down Grades!, proposed by Sunny Baggu:
=LET(_m,
UNIQUE(
C3:C37
),_c1,
{"A+";"A";"B";"C"},
_c2,
{1;2;3;4},_d,
EOMONTH(
B3,
SEQUENCE(
5,
,
0
)
),
_d1,
DROP(
_d,
-1
),
_d2,
DROP(
_d,
1
),HSTACK(VSTACK(
"Month",
_c2
),REDUCE({"Upgrade",
"No Change",
"Down-grade"},
SEQUENCE(
4
),LAMBDA(e,
f,
VSTACK(e,BYCOL(DROP(REDUCE("",
_m,
LAMBDA(x,
y,VSTACK(x,
LET(
_a,
TAKE(FILTER(D3:D37,
(B3:B37 <= INDEX(
_d1,
f,
1
)) * (C3:C37 = y)),
-1),_b,
TAKE(FILTER(D3:D37,
(MONTH(
B3:B37
) = MONTH(
INDEX(
_d2,
f,
1
)
)) * (YEAR(
B3:B37
) = YEAR(
INDEX(
_d2,
f,
1
)
)) * (C3:C37 = y)),
-1),_a1,
XLOOKUP(
_a,
_c1,
_c2,
,
,
-1
),_b1,
XLOOKUP(
_b,
_c1,
_c2,
,
,
-1
),_r,
HSTACK(
N(
_a1 > _b1
),
N(
_a1 = _b1
),
N(
_a1 < _b1
)
),
IFERROR(
_r,
0
))))),
1),LAMBDA(
a,
SUM(
a
)
)))))))Solving the challenge of P & Down Grades! with R
R solution 1 for P & Down Grades!, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("files/CH-035 Up and Down Grades.xlsx", range = "B2:D37")
test = read_excel("files/CH-035 Up and Down Grades.xlsx", range = "H2:K6")
result = input %>%
mutate(month = floor_date(Date, "month"),
Grade = factor(Grade, levels = c("A+","A", "B", "C"), ordered = TRUE)) %>%
summarise(last_grade = last(Grade), .by = c(`Agent-id`, month)) %>%
arrange(`Agent-id`, month) %>%
mutate(prev_grade = lag(last_grade), .by = `Agent-id`) %>%
filter(!is.na(prev_grade)) %>%
mutate(transition = case_when(
prev_grade > last_grade ~ "Upgrade",
prev_grade < last_grade ~ "Down-grade",
TRUE ~ "No change"
)) %>%
mutate(month = as.numeric(factor(month))) %>%
summarise(transitions = n(), .by = c(transition, month)) %>%
pivot_wider(names_from = transition, values_from = transitions, values_fill = 0) %>%
arrange(month) %>%
select(Month = month, Upgrade, `No change`, `Down-grade`)
Omid Motamedisedeh
There is one place where my results do not match and I checked it manually. In month 3 there is one down-grade: Agent AG-03 falls from A+ to A. R solution 2 for P & Down Grades!, proposed by Anil Kumar Goyal:
library(readxl)
library(janitor)
library(tidyverse)
grades <- data.frame(
grade = c("C", "B", "A", "A+"),
rank = 1:4
)
df <- read_xlsx("OM Challanges/CH-035 Up and Down Grades.xlsx",
range = cell_cols(LETTERS[2:4]),
skip = 1) %>%
row_to_names(1)
df %>%
clean_names() %>%
mutate(date = excel_numeric_to_date(as.numeric(date))) %>%
left_join(grades,
by = "grade") %>%
group_by(year = lubridate::year(date),
month = lubridate::month(date),
agent_id) %>%
slice_max(n = 1, order_by = date) %>%
ungroup() %>%
mutate(date = floor_date(date, "month")) %>%
mutate(change = c(NA, diff(rank)), .by = agent_id) %>%
drop_na(change) %>%
ungroup() %>%
mutate(change = case_when(change > 0 ~ "Upgrade",
change == 0 ~ "NoChange",
change < 0 ~ "Downgrade"),
month = month(date)) %>%
summarise(value = n(), .by = c(month, change)) %>%
pivot_wider(id_cols = month, values_from = value, names_from = change)