Dynamically generate a sales calendar for the month entered in F3, based on data from the given table. The calendar will display: “-” for days with zero sales, “U” for days where sales exceed the month’s average daily sales (calculating only days with sales), “L” for days where sales are below the month’s average. For example, in month 2, with the average sales equal to 19, days with sales greater than 19 are represented as “U”, and days with sales lower than 19 are denoted by “L”.
📌 Challenge Details and Links
Challenge Number: 18
Challenge Difficulty: ⭐⭐⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Sales Calendar Extraction! with Power Query
Power Query solution 1 for Sales Calendar Extraction!, proposed by Eric Laforce:
let
CWB = Excel.CurrentWorkbook(),
Source = CWB{[Name = "tSales"]}[Content],
m = CWB{[Name = "Month"]}[Content][Column1]{0},
FilterM = Table.SelectRows(
Table.TransformColumnTypes(Source, {{"Date", type date}}),
each Date.Month([Date]) = m
),
LDate = List.Buffer(FilterM[Date]),
LQty = List.Buffer(FilterM[Quantity]),
AvgQ = Number.RoundUp(List.Average(LQty)),
MFD = Date.StartOfMonth(LDate{0}),
MLD = Date.EndOfMonth(MFD),
FDOfWeeks = List.Generate(
() => MFD,
each _ <= MLD,
each Date.AddDays(Date.StartOfWeek(_, Day.Sunday), 7)
),
Transform = List.Transform(
FDOfWeeks,
each
let
ew = List.Min({Date.EndOfWeek(_, Day.Sunday), MLD}),
wdays = List.Dates(_, Duration.Days(ew - _) + 1, #duration(1, 0, 0, 0))
in
List.Accumulate(
wdays,
[],
(s, c) =>
let
QL = try if LQty{List.PositionOf(LDate, c)} > AvgQ then "U" else "L" otherwise "-"
in
Record.AddField(s, Date.ToText(c, [Format = "ddd", Culture = "en-us"]), QL)
)
),
CN = {"Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat"},
Result = Table.FromRecords(Transform, CN, MissingField.UseNull)
in
ResultPower Query solution 2 for Sales Calendar Extraction!, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S1 = Excel.CurrentWorkbook(){[Name = "T_1"]}[Content],
Fi = Excel.CurrentWorkbook(){[Name = "T_2"]}[Content],
C = Table.TransformColumnTypes(S1, {{"Date", type date}, {"Quantity", Int64.Type}}),
I1 = Table.AddColumn(C, "Month", each Date.Month([Date]), Int64.Type),
I2 = Table.AddColumn(I1, "Day Name", each Date.DayOfWeekName([Date]), type text),
I3 = Table.AddColumn(
I2,
"Week of Month",
each "w" & Text.From(Date.WeekOfMonth([Date])),
type text
),
G = Table.Group(
I3,
{"Month", "Day Name", "Week of Month"},
{{"TQ", each List.Sum([Quantity]), type nullable number}}
),
A = Table.AddColumn(
G,
"L",
each
if [TQ] >= List.Average(Table.SelectRows(I3, (I) => I[Month] = [Month])[Quantity]) then
"U"
else if [TQ] < List.Average(Table.SelectRows(I3, (I) => I[Month] = [Month])[Quantity]) then
"L"
else
"-"
),
F = Table.SelectRows(A, each [Month] = Fi[Month]{0}),
R = Table.SelectColumns(F, {"Week of Month", "Day Name", "L"}),
P = Table.Pivot(
R,
{"Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"},
"Day Name",
"L"
),
Sol = Table.ReplaceValue(
P,
null,
"-",
Replacer.ReplaceValue,
{"Week of Month", "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"}
)
in
SolPower Query solution 3 for Sales Calendar Extraction!, proposed by Glyn Willis:
let
MonthS=Excel.CurrentWorkbook(){[Name="Month"]}[Content][Column1]{0},
YearS=Excel.CurrentWorkbook(){[Name="Year"]}[Content][Column1]{0},
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Type = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Quantity", Int64.Type}}),
AddCol = Table.AddColumn(Type, "Cols", each let d=[Date] in [m=Date.Month(d),w=Date.WeekOfMonth(d,Day.Sunday),y=Date.Year(d),dw=Text.Start(Date.DayOfWeekName(d),3)]),
Expnd = Table.ExpandRecordColumn(AddCol, "Cols", {"dw", "m", "w", "y"}, {"dw", "m", "w", "y"}),
Filter = Table.SelectRows(Expnd,each [y]=YearS and [m]=MonthS),Solving the challenge of Sales Calendar Extraction! with Excel
Excel solution 1 for Sales Calendar Extraction!, proposed by Bo Rydobon 🇹🇭:
=LET(
z,
IFNA(
VSTACK(
B3:C121,
SEQUENCE(
365,
,
"1jan"
)
),
0
),
f,
FILTER(
z,
MONTH(
TAKE(
z,
,
1
)
)=F3
),
d,
TAKE(
f,
,
1
),
q,
DROP(
f,
,
1
),
w,
WEEKNUM(
d
), DROP(
PIVOTBY(
"W"&w-@w+1,
HSTACK(
WEEKDAY(
d
),
LEFT(
TEXT(
d,
"ddd"
),
2
)
),
IF(
q,
IF(
q>SUM(
q
)/SUM(
N(
q>0
)
),
"U",
"L"
),
"-"
),
SINGLE,
0,
0,
,
0
),
1
)
)Excel solution 2 for Sales Calendar Extraction!, proposed by 🇰🇷 Taeyong Shin:
=LET(d,
B3:B121,
q,
C3:C121,
s,
YEAR(
@+d
)&"-1",
dt,
SEQUENCE(365+(DAY(
EOMONTH(
s,
1
)
)<>28),
,
s),
func,
LAMBDA(
x,
y,
IF(
@x,
IF(
@x>AVERAGE(
y
),
"U",
"L"
),
"-"
)
),
pv,
PIVOTBY(
"W"&WEEKNUM(
dt
)-WEEKNUM(
EOMONTH(
dt,
-1
)+1
)+1,
HSTACK(
WEEKDAY(
dt
),
LEFT(
TEXT(
dt,
"ddd"
),
2
)
),
XLOOKUP(
dt,
d,
q,
FALSE
),
func,
,
,
,
0,
,
MONTH(
dt
)=F3
),
DROP(
DROP(
pv,
1
),
-1
))Excel solution 3 for Sales Calendar Extraction!, proposed by محمد حلمي:
=LET(
B,
B3:B121,
C,
C3:C121,
M,
F3,
D,
DATE(
2023,
M,
1
), R,
WRAPROWS(
VSTACK(
IF(
SEQUENCE(
WEEKNUM(
D
)-2
),
""
),
DATE(
2023,
M,
SEQUENCE(
DAY(
EDATE(
D,
1
)-1
)
)
)
),
7,
""
), V,
XLOOKUP(
R,
B,
C,
0
),
IFS(
R="",
"",
V=0,
"-", V>AVERAGE(
FILTER(
C,
MONTH(
B
)=M
)
),
"U",
1,
"L"
)
)Excel solution 4 for Sales Calendar Extraction!, proposed by Oscar Mendez Roca Farell:
=LET(
_d,
DAY(
FILTER(
B3:C121,
MONTH(
B3:B121
)=F3
)
),
_p,
ROUND(
AVERAGE(
DROP(
_d,
,
1
)
), ),
_s,
SEQUENCE(
5,
7,
,
1
)-WEEKDAY(
DATE(
2023,
F3,
1
),
2
), HSTACK(
VSTACK(
"",
"W"&SEQUENCE(
5
)
),
VSTACK(
TEXT(
SECUENCE(
,
7
),
"ddd"
),
IFNA(
IFS(
VLOOKUP(
_s,
_d,
2,
)>_p,
"U",
1,
"L"
),
"-"
)
)
)
)Excel solution 5 for Sales Calendar Extraction!, proposed by Julian Poeltl:
=WRAPROWS(LET(D,B3:B121,Q,C3:C121,MM,F3,MS,DATE(2023,F3,1),SQD,SEQUENCE(EOMONTH(MS,0)-MS+WEEKDAY(MS),,MS- WEEKDAY(MS)+1,1),QUA,IF(MONTH(SQD)=MM,XLOOKUP(SQD,D,Q,"-"),""),AV,AVERAGE(QUA),NUM,ISNUMBER(QUA),LA,QUA>=AV,ID(NUM=TRUE,IF(LA=TRUE,"U","L"),QUA)),7,"")Excel solution 6 for Sales Calendar Extraction!, proposed by Kris Jaganah:
=LET(a,
B3:B121,
b,
C3:C121,
c,
"W"&ROUNDUP(
DAY(
a
)/7,
0
),
d,
MONTH(
a
),
e,
WEEKDAY(
a
),
f,
LEFT(
TEXT(
e,
"ddd"
),
2
),
g,
MAP(d,
LAMBDA(x,
LET(a,
--(x=d),
SUM(
b*a
)/SUM(
a
)))),
h,
IF(
b>g,
"U",
"L"
),
DROP(
PIVOTBY(
c,
HSTACK(
e,
f
),
h,
CONCAT,
0,
0,
,
0,
,
d=F3
),
1
))Excel solution 7 for Sales Calendar Extraction!, proposed by John Jairo Vergara Domínguez:
=LET(v,
IFNA(
VSTACK(
B3:C121,
B3-1+ROW(
1:200
)
), ),
f,
TAKE(
v,
,
1
),
y,
DROP(
v,
,
1
),
c,
MONTH(
f
)=F3,
DROP(PIVOTBY("W"&1+WEEKNUM(
f
)-WEEKNUM(
1+f-DAY(
f
)
),
HSTACK(
WEEKDAY(
f
),
LEFT(
TEXT(
f,
"ddd"
),
2
)
),
y,
LAMBDA(x,
IF(SUM(
x
),
IF(SUM(
x
)>ROUND(AVERAGE(IF(c*(y>0),
y)),
),
"U",
"L"),
"-")),
,
0,
,
0,
,
c),
1))Excel solution 8 for Sales Calendar Extraction!, proposed by Charles Roldan:
=LET(Year,
2023,
Month,
F3,
Date,
B3:B121,
Quantity,
C3:C121,
FromTo,
LAMBDA(
a,
b,
SEQUENCE(
1 + b - a,
1,
a
)
),
NewYears,
DATE(
Year,
1,
1
),
FirstDay,
DATE(
Year,
Month,
1
),
LastDay,
EOMONTH(
FirstDay,
0
),
FirstWeek,
WEEKNUM(
FirstDay,
1
),
LastWeek,
WEEKNUM(
LastDay,
1
),
Days,
FromTo(
FirstDay,
LastDay
),
Weeks,
FromTo(
FirstWeek,
LastWeek
),
Weekdays,
SEQUENCE(
,
7
),
Qtys,
XLOOKUP(
Days,
Date,
Quantity
),
HSTACK(VSTACK("",
"W" & (Weeks - FirstWeek + 1)),
VSTACK(LEFT(
TEXT(
Weekdays,
"ddd"
),
2
),
XLOOKUP(NewYears - WEEKDAY(
NewYears
) + 1 + 7 * (Weeks - 1) + Weekdays,
Days,
IFNA(
IF(
Qtys >= AVERAGE(
TOCOL(
Qtys,
2
)
),
"U",
"L"
),
"-"
),
""))))Excel solution 9 for Sales Calendar Extraction!, proposed by Gowthaman V:
=LET(a,
B3:B121,
b,
C3:C121,
c,
F3,
e,
AVERAGE(
FILTER(
b,
MONTH(
a
)=c
)
),
d,
DATE(
2023,
c,
1
),
g,
WEEKDAY(
d
),
f,
SEQUENCE(
DAY(
EOMONTH(
d,
0
)
)+g-1
),
IFERROR(IFNA(INDEX(IF(
g<=f,
IF(
XLOOKUP(
d+f-g,
a,
b,
,
0
)>=e,
"U",
"L"
),
""
),
SEQUENCE(ROUNDUP(((MAX(
f
))/7),
0),
7),
1),
"-"),
""))Excel solution 10 for Sales Calendar Extraction!, proposed by Hussein SATOUR:
=LET(q,C3:C121,da,B3:B121,a,SEQUENCE(365,,"01/01/2023"),b,WEEKNUM(a),f,INDEX(b,XMATCH(DATE(2023,F3,1),a)),h,WRAPROWS(FILTER(IF(MONTH(a)=F3,XLOOKUP(a,da,q,"-"),""),(b>=f)*(b<f+5)),7),VSTACK(TOROW(VSTACK("",UNIQUE(TEXT(a,"ddd")))),HSTACK("W"&SEQUENCE(5),IFS(h="","",h="-","-",h<AVERAGE(FILTER(q,MONTH(da)=F3)),"L",1,"U"))))Excel solution 11 for Sales Calendar Extraction!, proposed by Nicolas Micot:
=SI(ET(_dateDebut+7*(LIGNE()-LIGNE(
$I$3
))+COLONNE()-COLONNE(
$I$3
)>=_debutMois;
_dateDebut+7*(LIGNE()-LIGNE(
$I$3
))+COLONNE()-COLONNE(
$I$3
)<=_finMois);
SI(SOMME.SI(_Date;
_dateDebut+7*(LIGNE()-LIGNE(
$I$3
))+COLONNE()-COLONNE(
$I$3
);
_Quantity)=0;
"-";
SI(SOMME.SI(_Date;
_dateDebut+7*(LIGNE()-LIGNE(
$I$3
))+COLONNE()-COLONNE(
$I$3
);
_Quantity)<_monthAverageSale;
"L";
"U"));
"")
Names:
_Date : =Feuil2!$B$3:$B$121
_dateDebut : =DATE(
2023;
_Month;
1
)-JOURSEM(
_debutMois;
1
)+1
_debutMois : =DATE(
2023;
_Month;
1
)
_finMois : =FIN.MOIS(
DATE(
2023;
_Month;
1
);
0
)
_Month : =Feuil2!$F$3
_monthAverageSale : =ARRONDI(
MOYENNE.SI.ENS(
_Quantity;
_Date;
">="&_debutMois;
_Date;
"<="&_finMois
);
0
)Solving the challenge of Sales Calendar Extraction! with R
R solution 1 for Sales Calendar Extraction!, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
library(padr)
input = read_excel("CH-018 Sales Calendar Extraction.xlsx", range = "B2:C121")
test_month = 2
test = read_excel("CH-018 Sales Calendar Extraction.xlsx", range = "I2:O7")
result = input %>%
pad() %>%
hashtag
#fill dataseries with missing dates
mutate(month = month(Date),
wday = wday(Date, abbr = TRUE, label = TRUE, locale = "English"),
week = week(Date)) %>%
group_by(month) %>%
mutate(monthly_av = mean(Quantity[!is.na(Quantity)], na.rm = TRUE) %>%
round(0)) %>%
ungroup() %>%
filter(month == test_month) %>%
mutate(Quantity_check = case_when(Quantity <= monthly_av ~ "L",
Quantity > monthly_av ~ "U",
.default = "-")) %>%
select(wday, week, Quantity_check) %>%
pivot_wider(names_from = wday, values_from = Quantity_check,
values_fill = list(Quantity_check = NA)) %>%
select(Su= Sun, Mo = Mon, Tu = Tue, We = Wed, Th = Thu, Fr = Fri,Sa = Sat)
all.equal(test, result)
# [1] TRUE