Home » Weekly Count by Category

Weekly Count by Category

Week 1 starts from 1st of the month. Hence, 1st Oct to 7th Oct is Week 1, 8th to 14th is Week2 and so on. In Answer, first column is Week number, headers are values given in column B and fields are counts of those values.

📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 572
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Weekly Count by Category with Power Query

Power Query solution 1 for Weekly Count by Category, proposed by Kris Jaganah: 
let
  A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  B = Table.TransformColumns(
    A, 
    {{"Date", each Number.IntegerDivide((Date.Day(_) - 1), 7) + 1}, {"Value", Text.From}}
  ), 
  C = Table.DuplicateColumn(B, "Value", "V"), 
  D = Table.Pivot(C, List.Sort(List.Distinct(C[Value])), "Value", "V", List.Count), 
  E = Table.RenameColumns(D, {"Date", "Week"})
in
  E
Power Query solution 2 for Weekly Count by Category, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Week = Table.AddColumn(Source, "Week", each Number.RoundUp((Date.Day([Date])) / 7)), 
  Group = Table.Group(
    Week, 
    {"Week"}, 
    {
      {
        "A", 
        each Table.PromoteHeaders(
          Table.Transpose(Table.Group(_, {"Value"}, {{"B", each Table.RowCount(_)}}))
        )
      }
    }
  ), 
  Sol = Table.ExpandTableColumn(Group, "A", List.Transform(Group[Week], Text.From))
in
  Sol
Power Query solution 3 for Weekly Count by Category, proposed by Abdallah Ally: 
let
  Source    = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  AddCol    = Table.AddColumn(Source, "Week", each Number.RoundUp(Date.Day([Date]) / 7)), 
  Transform = Table.TransformColumnTypes(AddCol, {"Value", type text}), 
  Group     = Table.Group(Transform, {"Week", "Value"}, {"Data", each List.Count([Value])}), 
  ToPivot   = List.Distinct(List.Sort(Group[Value])), 
  Result    = Table.Pivot(Group, ToPivot, "Value", "Data", each List.Sum(_) ?? 0)
in
  Result
Power Query solution 4 for Weekly Count by Category, proposed by Ramiro Ayala Chávez: 
let
  S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  a = Table.AddColumn(S, "D", each Date.Day([Date])), 
  b = Table.AddColumn(
    a, 
    "Week", 
    each 
      let
        c = {
          [D] >= 1 and [D] <= 7, 
          [D] >= 8 and [D] <= 14, 
          [D] >= 15 and [D] <= 21, 
          [D] >= 22 and [D] <= 29, 
          [D] > 29
        }, 
        d = {1 .. 5}, 
        e = d{List.PositionOf(c, true)}
      in
        e
  )[[Week], [Value], [D]], 
  f = Table.TransformColumnTypes(Table.Sort(b, {"Value", 0}), {"Value", type text}), 
  Sol = Table.Pivot(f, List.Distinct(f[Value]), "Value", "D", List.Count)
in
  Sol

Solving the challenge of Weekly Count by Category with Excel

Excel solution 1 for Weekly Count by Category, proposed by Bo Rydobon 🇹🇭: 
=LET(
    p,
    PIVOTBY(
        ROUNDUP(
            DAY(
                A2:A14
            )/7,
            
        ),
        B2:B14,
        B2:B14,
        ROWS,
        ,
        0,
        ,
        0
    ),
    HSTACK(
        VSTACK(
            "Week",
            TAKE(
                p,
                -5,
                1
            )
        ),
        DROP(
            IF(
                p="",
                0,
                p
            ),
            ,
            1
        )
    )
)
Excel solution 2 for Weekly Count by Category, proposed by John V.: 
=IF(SEQUENCE(6,6)=1,"Week",--(0&PIVOTBY(1+INT((DAY(A2:A14)-1)/7),B2:B14,B2:B14,ROWS,,0,,0)))
Excel solution 3 for Weekly Count by Category, proposed by 🇰🇷 Taeyong Shin: 
=LET(p,N(PIVOTBY(CEILING(DAY(A2:A14)/7,1),B2:B14,B2:B14,ROWS,,0,,0)),IF(SEQUENCE(ROWS(p),COLUMNS(p))=1,"Week",p))
Excel solution 4 for Weekly Count by Category, proposed by Kris Jaganah: 
=PIVOTBY(INT(DAY(A2:A14-1)/7)+1,B2:B14,B2:B14,COUNT,,0,,0)
Excel solution 5 for Weekly Count by Category, proposed by Julian Poeltl: 
=LET(
    DV,
    A2:A14,
    V,
    B2:B14,
    D,
    DATE(
        2024,
        10,
        SEQUENCE(
            5,
            ,
            ,
            7
        )
    ),
    S,
    SEQUENCE(
        ,
        MAX(
            V
        )
    ),
    HSTACK(
        VSTACK(
            "Week",
            SEQUENCE(
                5
            )
        ),
        VSTACK(
            S,
            COUNTIFS(
                V,
                SEQUENCE(
                    ,
                    5
                ),
                DV,
                ">="&D,
                DV,
                "<"&VSTACK(
                    DROP(
                        D,
                        1
                    ),
                    TAKE(
                        D,
                        -1
                    )+7
                )
            )
        )
    )
)
Excel solution 6 for Weekly Count by Category, proposed by Aditya Kumar Darak 🇮🇳: 
=PIVOTBY(QUOTIENT(DAY(A2:A14) - 1, 7) + 1, B2:B14, B2:B14, ROWS, 0, 0, , 0)
Excel solution 7 for Weekly Count by Category, proposed by Timothée BLIOT: 
=PIVOTBY(CEILING(DAY(A2:A14)/7,1),B2:B14,B2:B14,COUNT,,0,,0)
Excel solution 8 for Weekly Count by Category, proposed by Hussein SATOUR: 
=MAKEARRAY(5,5,LAMBDA(x,y,SUM((ROUNDUP(DAY(A2:A14)/7,0)=x)*(B2:B14=y))))
Excel solution 9 for Weekly Count by Category, proposed by Duy Tùng: 
=LET(a,
    PIVOTBY(INT((DAY(
        A2:A14
    )-1)/7)+1,
    B2:B14,
    B2:B14,
    ROWS,
    ,
    0,
    ,
    0),
    IF(
        a="",
        IF(
            TAKE(
                a,
                ,
                1
            )="",
            "Week",
            0
        ),
        a
    ))
Excel solution 10 for Weekly Count by Category, proposed by Sunny Baggu: 
=LET(
 _d, DAY(A2:A14),
 _w, ROUNDUP(_d / 7, 0),
 _v, B2:B14,
 _wm, SEQUENCE(MAX(_w)),
 _nmax, SEQUENCE(, MAX(B2:B14)),
 _val, MAKEARRAY(
 ROWS(_wm),
 COLUMNS(_nmax),
 LAMBDA(r, c,
 INDEX(BYCOL((_w = r) * (_v = _nmax), LAMBDA(a, SUM(a))), c)
 )
 ),
 VSTACK(HSTACK("Week", _nmax), HSTACK(_wm, _val))
)
Excel solution 11 for Weekly Count by Category, proposed by Abdallah Ally: 
=LET(a,B2:B14,b,DROP(PIVOTBY(MAP(A2:A14,LAMBDA(x,MOD( WEEKNUM(x,12),40))),a,a,COUNT,0,0,,0),1),VSTACK(HSTACK("Week",TOROW(SORT(UNIQUE(a)))),IF(b="",0,b)))
Excel solution 12 for Weekly Count by Category, proposed by Abdallah Ally: 
=LET(
    a,
    B2:B14,
    b,
    PIVOTBY(
        ROUNDUP(
            DAY(
                A2:A14
            )/7,
            0
        ),
        a,
        a,
        COUNT,
        0,
        0,
        ,
         0
    ),
    VSTACK(
        HSTACK(
            "Week",
            TAKE(
                b,
                1,
                -5
            )
        ),
        DROP(
            IF(
                b="",
                0,
                b
            ),
            1
        )
    )
)
Excel solution 13 for Weekly Count by Category, proposed by Md. Zohurul Islam: 
=LET(
 a, A2:A14,
 b, B2:B14,
 c, DAY(a),
 d, CEILING(c / 7, 1),
 rng, ABS(d & b),
 x, SORT(UNIQUE(b)),
 y, SEQUENCE(, COUNTA(x)),
 lokupVal, TOCOL(ABS(x & y)),
 myVal, MAP(lokupVal, LAMBDA(x, SUM(IFNA(XMATCH(rng, x), 0)))),
 e, WRAPROWS(myVal, 5),
 f, VSTACK(y, e),
 g, VSTACK("Week", x),
 result, HSTACK(g, f),
 result
)
Excel solution 14 for Weekly Count by Category, proposed by Md. Zohurul Islam: 
=LET(
x,A2:A14,
a,CEILING(DAY(x)/7,1),
b,B2:B14,
d,PIVOTBY(a,b,b,COUNT,0,0,,0),
p,VSTACK("Week",DROP(TAKE(d,,1),1)),
q,TAKE(d,,-5),
r,HSTACK(p,q),
result,IF(r="",0,r),
result)
Excel solution 15 for Weekly Count by Category, proposed by Pieter de B.: 
=LET(
    p,
    IFERROR(
        --PIVOTBY(
            ROUNDUP(
                DAY(
                    A2:A14
                )/7,
                
            ),
            B2:B14,
            B2:B14,
            ROWS,
            ,
            0,
            ,
            0
        ),
        0
    ),
    IF(
        SEQUENCE(
            ROWS(
                p
            ),
            COLUMNS(
                p
            ),
            0
        ),
        p,
        "Week"
    )
)
Excel solution 16 for Weekly Count by Category, proposed by Hamidi Hamid: 
=LET(x,MAP(A2:A14-1,LAMBDA(a,WEEKNUM(a,2)))-WEEKNUM(A2,2)+1,PIVOTBY(x,B2:B14,B2:B14,COUNT,0,0,,0))
Excel solution 17 for Weekly Count by Category, proposed by ferhat CK: 
=LET(r,PIVOTBY(XLOOKUP(DAY(A2:A14),{7,14,21,27,31},SEQUENCE(,5),,1),B2:B14,B2:B14,COUNT,,0,,0),IF(r="",0,r))
Excel solution 18 for Weekly Count by Category, proposed by Andy Heybruch: 
=PIVOTBY(
    ROUNDUP(
        DAY(
            A2:A14
        )/7,
        0
    ),
    B2:B14,
    B2:B14,
    COUNTA,
    ,
    0,
    ,
    0
)
Excel solution 19 for Weekly Count by Category, proposed by Ankur Sharma: 
=LET(a, ROUNDUP(DAY(A2:A14)/7, 0), TEXTSPLIT(ARRAYTOTEXT(MAP(D3:D7, LAMBDA(Wk, TEXTJOIN("-", , MAP(E2:I2, LAMBDA(Va, SUM((a = Wk) * (B2:B14 = Va)))))))), "-", ", "))
Excel solution 20 for Weekly Count by Category, proposed by Julien Lacaze: 
=PIVOTBY(1+QUOTIENT(DAY(A2:A14)-1,7),B2:B14,B2:B14,COUNT,0,0,,0)
Excel solution 21 for Weekly Count by Category, proposed by Edwin Tisnado: 
=LET(s,
    SEQUENCE(
        ,
        5
    ),
    l,
    TOCOL(
        s
    ),
    HSTACK(VSTACK(
        "Week",
        l
    ),
    REDUCE(s,
    l,
    LAMBDA(x,
    y,
    VSTACK(x,
    BYCOL((ROUND(
        DAY(
            A2:A14
        )/7+0.5,
        
    )=y)*(B2:B14=s),
    SUM))))))
Excel solution 22 for Weekly Count by Category, proposed by Philippe Brillault: 
=LET(d,t_i,w,IF(COLUMN(d)=1,INT((DAY(d)-1)/7)+1,d),p,PIVOTBY(TAKE(w,,1),TAKE(w,,-1),TAKE(w,,-1),COUNT,0,0,,0),WRAPROWS(VSTACK("Week",DROP(TOCOL(p),1,)),COLUMNS(p)))
Excel solution 23 for Weekly Count by Category, proposed by Songglod P.: 
=LET(
    d,
    ROUNDUP(
        DAY(
            A2:A14
        )/7,
        0
    ),
    v,
    d&B2:B14,
    arr,
    TOCOL(
        MAKEARRAY(
            5,
            5,
            LAMBDA(
                r,
                c,
                r&c
            )
        )
    ),
    WRAPROWS(
        MAP(
            arr,
            LAMBDA(
                r,
                SUM(
                    FILTER(
                        v/v,
                        v=r,
                        0
                    )
                )
            )
        ),
        5
    )
)

Solving the challenge of Weekly Count by Category with Python

Python solution 1 for Weekly Count by Category, proposed by Konrad Gryczan, PhD: 
import pandas as pd
import numpy as np
path = "572 Pivot Problem.xlsx"
input = pd.read_excel(path, usecols="A:B", nrows=14)
test = pd.read_excel(path, usecols="D:I", nrows=5, skiprows=1)
input['Week'] = np.ceil((pd.to_datetime(input['Date']).dt.day - 1) // 7 + 1).astype("int64")
result = input.pivot_table(index='Week', columns='Value', values='Date', aggfunc='size', fill_value=0).reset_index().rename_axis(None, axis=1)
result = result[['Week', 1, 2, 3, 4, 5]]
test.columns = result.columns
print(result.equals(test)) # True

Solving the challenge of Weekly Count by Category with Python in Excel

Python in Excel solution 1 for Weekly Count by Category, proposed by Alejandro Campos: 
df = xl("A1:B14", headers=True)
df['Date'] = pd.to_datetime(df['Date'], format='%d/%m/%Y')
def get_week_number(date):
 day = date.day
 return (day - 1) // 7 + 1
df['Week'] = df['Date'].apply(get_week_number)
result = df.pivot_table(index='Week', columns='Value', aggfunc='size', fill_value=0)
result = result.reset_index()
result.columns.name = None
result
Python in Excel solution 2 for Weekly Count &by Category, proposed by Abdallah Ally: 
from math import ceil
df = xl("A1:B14", headers=True)
# Perform data wrangling
df = (
 df
 .assign(Week = (df['Date'].dt.day / 7).map(ceil))
 .pivot_table(
 index='Week', 
 columns='Value', 
 values='Value', 
 aggfunc='count', 
 fill_value=0
 )
 .reset_index()
)
df.columns.name = None
df
Python in Excel solution 3 for Weekly Count by Category, proposed by Anshu Bantra: 
df = xl("A1:B14", headers=True)
df['Week'] = ((df['Date'].dt.day // 7.5)+1)
df = pd.DataFrame(pd.crosstab(df['Week'], df['Value'],).reset_index())
df.columns.name = None
df

Solving the challenge of Weekly Count by Category with R

R solution 1 for Weekly Count by Category, proposed by Konrad Gryczan, PhD: 
library(tidyverse)
library(readxl)
library(lubridate)
path = "Excel/572 Pivot Problem.xlsx"
input = read_excel(path, range = "A1:B14")
test = read_excel(path, range = "D2:I7")
result = input %>%
 mutate(Date = as.Date(Date),
 Week = ceiling((day(Date) - 1) %/% 7 + 1)) %>%
 pivot_wider(names_from = Value, values_from = Date, values_fn = length, values_fill = 0) %>%
 select(Week, `1`,`2`,`3`,`4`,`5`) 
all.equal(result, test)
#> [1] TRUE

&&

Leave a Reply