In challenge 60, we attempted to calculate the source of payment for each receipt. In this challenge, we are going to calculate the average duration of payment for each receipt. The customer receipt costs and their payments are provided in tables 1 and 2. We will match the payment to the receipt based on the date and calculate the payment duration for each receipt. For example, all of receipt C1 is paid by Payment P1, 47 days before the receipt. For receipt C5, out of the total $374, $337 is paid by P4 (49 days before) and $37 is paid by P5 (27 days before), resulting in an average payment duration of -47.8 days.
📌 Challenge Details and Links
Challenge Number: 120
Challenge Difficulty: ⭐⭐⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Payments Duration! with Power Query
Power Query solution 1 for Payments Duration!, proposed by Zoran Milokanović:
let
Source = each Excel.CurrentWorkbook(){[Name = _]}[Content],
T = Source("Table1"),
S = Table.FromRows(
Table.TransformRows(
T,
each
let
d = List.FirstN(
List.Skip(
List.TransformMany(Table.ToRows(Source("Table2")), each {1 .. _{2}}, (i, _) => i{1}),
List.Sum(List.FirstN(T[Cost], Table.PositionOf(T, _))) ?? 0
),
[Cost]
)
in
{
[ID],
{
"NP",
List.Sum(
List.Transform(
List.Distinct(d),
(m) => Number.From(m - [Date]) * List.Count(List.PositionOf(d, m, 2))
)
)
/ List.Count(d)
}{Byte.From(List.Count(d) = [Cost])}
}
),
{"Recipt ID", "Duration"}
)
in
SPower Query solution 2 for Payments Duration!, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Tbl1 = List.Combine(Table.AddColumn(Source, "A", each List.Repeat({[ID]}, [Cost]))[A]),
Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
Tbl2 = List.Combine(Table.AddColumn(Source2, "A", each List.Repeat({[ID]}, [Payment]))[A]),
Tbl = Table.FromColumns({Tbl1, Tbl2}),
Group = Table.Group(Tbl, {"Column1"}, {{"A", each
let
a = _,
b = Table.Group(a, "Column2", {{"B", each List.Count([Column1])}}),
c = Table.SelectRows(b, each [Column2]<>null),
d = Table.PromoteHeaders(Table.Transpose(c)),
e = if Table.IsEmpty(d) then Table.FromRows({List.Repeat({"NP"},
List.Count(List.Distinct(Tbl2)))}, List.Distinct(Tbl2)) else d
in e}}),
Expand = Table.ExpandTableColumn(Group, "A", List.Distinct(Tbl2)),
Sol = Table.AddColumn(Expand, "Duration", (y)=>
let
a = List.Transform(List.Skip(Record.ToList(y)), (x)=> Replacer.ReplaceValue(x, null, 0)),
b = List.Transform(Source2[Date], Date.From),
c = Record.ToList(y){0},
d = Date.From(Table.SelectRows(Source, each [ID]=c)[Date]{0}),
e = List.Transform(b, each Number.From(_-d)/List.Sum(a)),
f = try List.Sum(List.Transform(List.Zip({a,e}), each List.Product(_))) otherwise "NP"
in f)[[Column1], [Duration]]
in
SolSolving the challenge of Payments Duration! with Excel
Excel solution 1 for Payments Duration!, proposed by Bo Rydobon 🇹🇭:
=LET(p,
H3:H7,
q,
SCAN(
,
p,
SUM
)-p,IFERROR(MAP(D3:D14,
C3:C14,
LAMBDA(c,
d,LET(i,
SUM(
D3:c
)-q,
L,
LAMBDA(j,
IF(j0)*j,
p)),v,
L(
i
)-L(
i-c
),
SUM(v*(G3:G7-d))/SUM(
v
)))),
"NP")) Excel solution 2 for Payments Duration!, proposed by Julian Poeltl:
=LET(C,
D3:D14,
D,
C3:C14,
DP,
G3:G7,
P,
H3:H7,
RP,
SCAN(
0,
P,
SUM
),
RC,
SCAN(
0,
C,
SUM
),
BF,
RC-C,
IFNA(MAP(RC,
BF,
C,
D,
LAMBDA(A,
B,
C,
D,
LET(F,
DROP(
TAKE(
HSTACK(
DP,
RP
),
XMATCH(
A,
RP,
1
)
),
IFNA(
XMATCH(
B,
RP,
-1
),
0
)
),
M,
MAP((TAKE(
F,
,
-1
)-B),
LAMBDA(
A,
MIN(
A,
C
)
)),
SUM((TAKE(
F,
,
1
)-D)*(M-DROP(
VSTACK(
0,
M
),
-1
)))/C))),
"NP"))Solving the challenge of Payments Duration! with Python in Excel
Python in Excel solution 1 for Payments Duration!, proposed by Alejandro Campos:
df, pay_df = xl("B2:D14", headers=True), xl("F2:H7", headers=True)
rec_df['Date'], pay_df['Date'] = pd.to_datetime(rec_df['Date'], dayfirst=True), pd.to_datetime(pay_df['Date'], dayfirst=True)
rem = pay_df[['ID', 'Payment']].assign(Remaining=lambda df: df['Payment'])
results = []
for _, r in rec_df.iterrows():
cost, date, durations, contributions = r['Cost'], r['Date'], [], []
for i, p in rem.iterrows():
if p.Remaining > 0:
amt = min(p.Remaining, cost)
durations.append((pay_df.at[i, 'Date'] - date).days)
contributions.append(amt)
rem.at[i, 'Remaining'], cost = p.Remaining - amt, cost - amt
if cost == 0: break
results.append({'Receipt ID': r['ID'], 'Duration': sum(d * a for d, a in zip(durations, contributions)) / sum(contributions) if contributions else 'NP'})
pd.DataFrame(results)Solving the challenge of Payments Duration! with R
R solution 1 for Payments Duration!, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "files/CH-120 payments Durations.xlsx"
input1 = read_excel(path, range = "B2:D14")
input2 = read_excel(path, range = "F2:H7")
test = read_excel(path, range = "K2:L14")
test = test %>%
mutate(Duration = ifelse(is.na(as.numeric(Duration)), Duration, as.character(round(as.numeric(Duration), 0))))
rec = input1 %>%
mutate(ID = factor(ID, levels = paste0("C", 1:12), ordered = TRUE)) %>%
uncount(Cost, .remove = FALSE) %>%
mutate(Cost = 1, rn = row_number())
pay = input2 %>%
mutate(ID = factor(ID, levels = paste0("P", 1:5), ordered = TRUE)) %>%
uncount(Payment, .remove = FALSE) %>%
mutate(Payment = 1, rn = row_number())
all = full_join(rec, pay, by = "rn") %>%
mutate(pay_time = Date.y - Date.x) %>%
summarise(amount = sum(Cost), .by = c(ID.x, ID.y, pay_time)) %>%
summarise(mean_time = as.character(round(sum(pay_time * amount) / sum(amount)),0), .by = ID.x) %>%
replace_na(list(mean_time = "NP")) %>%
mutate(ID.x = as.character(ID.x))
all.equal(test, all, check.attributes = FALSE)
#> [1] TRUE
Solving the challenge of Payments Duration! with Google Sheets
Google Sheets solution 1 for Payments Duration!, proposed by Peter Krkos:
PowerQuery solution:
https://docs.google.com/spreadsheets/d/1zR5IZLz8OT76vhaPEHfsPrw8-RDKnLyyqS49IJjdhFk/edit?pli=1&gid=1279498181#gid=1279498181