Home » Top Three Sales by Date

Top Three Sales by Date

Find the top 3 highest sales made on a date and list along with Customer Name and Date.

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

Solving the challenge of Top Three Sales by Date with Power Query

Power Query solution 1 for Top Three Sales by Date, proposed by Kris Jaganah: 
let
  A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  B = Table.Group(A, {"Customer", "Date"}, {"Amount", each List.Sum([Amount])}), 
  C = Table.SelectRows(B, each ([Amount] >= List.Min(List.MaxN(List.Distinct(B[Amount]), 3)))), 
  D = Table.Sort(C, {{"Amount", 1}, {"Customer", 0}})
in
  D
Power Query solution 2 for Top Three Sales by Date, proposed by Vida Vaitkunaite: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Group1 = Table.Group(Source, {"Customer", "Date"}, {{"Sum", each List.Sum([Amount])}}), 
  Group2 = Table.MaxN(Table.Group(Group1, {"Sum"}, {{"All", each _}}), "Sum", 3), 
  Final  = Table.RenameColumns(Table.Combine(Group2[All]), {{"Customer", "Name"}})
in
  Final
Power Query solution 3 for Top Three Sales by Date, proposed by Abdallah Ally: 
let
  Source       = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Group        = Table.Group(Source, {"Customer", "Date"}, {"Amount", each List.Sum([Amount])}), 
  ThirdLargest = List.LastN(List.Sort(List.Distinct(Group[Amount])), 3){0}, 
  Sort         = Table.Sort(Table.SelectRows(Group, each [Amount] >= ThirdLargest), each - [Amount]), 
  Transform    = Table.TransformColumnTypes(Sort, {"Date", type date}), 
  Result       = Table.RenameColumns(Transform, {"Customer", "Name"})
in
  Result
Power Query solution 4 for Top Three Sales by Date, proposed by Seokho MOON: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Rename = Table.RenameColumns(Source, {"Customer", "Name"}), 
  Group = Table.Group(Rename, {"Name", "Date"}, {"Amount", each List.Sum([Amount])}), 
  Res = Table.MaxN(
    Group, 
    "Amount", 
    each [Amount] >= List.Min(List.MaxN(List.Distinct(Group[Amount]), 3))
  )
in
  Res
Power Query solution 5 for Top Three Sales by Date, proposed by CA Raghunath Gundi: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Grp = Table.Group(
    Source, 
    {"Customer", "Date"}, 
    {{"Amount", each List.Sum([Amount]), type number}}
  ), 
  Rank = Table.AddRankColumn(Grp, "Rank", {"Amount", Order.Descending}, [RankKind = RankKind.Dense]), 
  Rows = Table.RemoveColumns(Table.SelectRows(Rank, each [Rank] <= 3), "Rank")
in
  Rows
Power Query solution 6 for Top Three Sales by Date, proposed by Krzysztof Kominiak: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  GroupRows1 = Table.Group(
    Source, 
    {"Customer", "Date"}, 
    {{"SA", each List.Sum([Amount]), type number}}
  ), 
  GroupRows2 = Table.Sort(
    Table.Group(GroupRows1, {"SA"}, {{"NT", each _, type table}}), 
    {{"SA", Order.Descending}}
  ), 
  Result = Table.Combine(Table.FirstN(GroupRows2, 3)[NT])
in
  Result
Power Query solution 7 for Top Three Sales by Date, proposed by Krupesh Bhansali: 
let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 #"Grouped Rows" = Table.Group(Source, {"Customer", "Date"}, {{"Amount", each List.Sum([Amount]), type number}}),
 SelctionCriteria = List.Min(List.MaxN(List.Distinct(#"Grouped Rows"[Amount]),3)),
 SelectionRow = Table.Sort(Table.SelectRows(#"Grouped Rows",(f) => f[Amount]>= SelctionCriteria),{"Amount",Order.Descending}),
 #"Changed Type" = Table.TransformColumnTypes(SelectionRow,{{"Customer", type text}, {"Date", type date}, {"Amount", Int64.Type}})
in
 #"Changed Type

Solving the challenge of Top Three Sales by Date with Excel

Excel solution 1 for Top Three Sales by Date, proposed by Bo Rydobon 🇹🇭: 
=LET(
    g,
    GROUPBY(
        A2:B20,
        C2:C20,
        SUM,
        ,
        0,
        -3,
        ,
        1
    ),
    a,
    DROP(
        g,
        ,
        2
    ),
    FILTER(
        g,
        a>LARGE(
            UNIQUE(
                a
            ),
            4
        )
    )
)
Excel solution 2 for Top Three Sales by Date, proposed by John V.: 
=LET(s,SORT(GROUPBY(A2:B20,C2:C20,SUM,,0),3,-1),d,DROP(s,,2),FILTER(s,d>LARGE(UNIQUE(d),4)))
Excel solution 3 for Top Three Sales by Date, proposed by 🇰🇷 Taeyong Shin: 
=LET(g,GROUPBY(A2:B20,C2:C20,SUM,,0),s,DROP(g,,2),FILTER(g,s>-INDEX(GROUPBY(-s,,),4)))
Excel solution 4 for Top Three Sales by Date, proposed by Kris Jaganah: 
=LET(
    a,
    A2:A20,
    b,
    B2:B20,
    c,
    SUMIFS(
        C2:C20,
        b,
        b,
        a,
        a
    ),
    SORT(
        UNIQUE(
            FILTER(
                HSTACK(
                    A2:B20,
                    c
                ),
                c>LARGE(
                    UNIQUE(
                        c
                    ),
                    4
                )
            )
        ),
        {3,
        1},
        {-1,
        1}
    )
)
Excel solution 5 for Top Three Sales by Date, proposed by Julian Poeltl: 
=LET(T,A2:B20,U,UNIQUE(T),V,C2:C20,C,BYROW(T,LAMBDA(A,CONCAT(A))),S,MAP(UNIQUE(C),LAMBDA(A,SUM(FILTER(V,C=A)))),SORT(FILTER(HSTACK(U,S),S>LARGE(UNIQUE(S),4)),3,-1))
Excel solution 6 for Top Three Sales by Date, proposed by Alejandro Campos: 
=VSTACK(
 HSTACK("Name", B1:C1),
 SORT(
 UNIQUE(
 FILTER(
 HSTACK(A2:A20, B2:B20, SUMIFS(C2:C20, A2:A20, A2:A20, B2:B20, B2:B20)),
 ISNUMBER(
 XMATCH(
 SUMIFS(C2:C20, A2:A20, A2:A20, B2:B20, B2:B20),
 LARGE(
 UNIQUE(SUMIFS(C2:C20, A2:A20, A2:A20, B2:B20, B2:B20)),
 SEQUENCE(3)))))),
 3,
 -1))
Excel solution 7 for Top Three Sales by Date, proposed by Aditya Kumar Darak 🇮🇳: 
=LET(
    
     _grp,
     GROUPBY(
         A2:B20,
          C2:C20,
          SUM,
          0,
          0,
          -3,
          ,
          1
     ),
    
     _large,
     LARGE(
         UNIQUE(
             TAKE(
                 _grp,
                  ,
                  -1
             )
         ),
          3
     ),
    
     _return,
     FILTER(
         _grp,
          TAKE(
                 _grp,
                  ,
                  -1
             ) >= _large
     ),
    
     _return
    
)
Excel solution 8 for Top Three Sales by Date, proposed by Hussein SATOUR: 
=LET(g,SORT(GROUPBY(A2:B20,C2:C20,SUM,,0),3,-1),a,INDEX(g,,3),FILTER(g,a>LARGE(UNIQUE(a),4)))
Excel solution 9 for Top Three Sales by Date, proposed by Oscar Mendez Roca Farell: 
=LET(
    a,
    A2:A20,
    b,
    B2:B20,
    c,
    C2:C20,
    s,
    SUMIFS(
        c,
        a,
        a,
        b,
        b
    ),
    GROUPBY(
        A2:B20,
        c,
        SUM,
        ,
        0,
        -2,
        s>LARGE(
            UNIQUE(
                s
            ),
            4
        )
    )
)
Excel solution 10 for Top Three Sales by Date, proposed by Sunny Baggu: 
=LET(
 _n, UNIQUE(A2:A20),
 _r, SORT(
 DROP(
 REDUCE(
 "",
 _n,
 LAMBDA(x, y,
 VSTACK(
 x,
 LET(
 _nf, SORT(FILTER(B2:C20, A2:A20 = y)),
 _d, UNIQUE(TAKE(_nf, , 1)),
 _a, MAP(_d, LAMBDA(a, SUM(FILTER(TAKE(_nf, , -1), TAKE(_nf, , 1) = a)))),
 IFNA(HSTACK(y, SORT(HSTACK(_d, _a), 2, 1)), y)
 )
 )
 )
 ),
 1
 ),
 3,
 -1
 ),
 SORT(FILTER(_r, TAKE(_r, , -1) >= LARGE(TAKE(_r, , -1), 4)))
)
Excel solution 11 for Top Three Sales by Date, proposed by LEONARD OCHEA 🇷🇴: 
=LET(G,GROUPBY,I,INDEX,a,G(A2:B20,C2:C20,SUM,,0,-3,,1),b,I(a,,3),FILTER(a,b>-I(G(-b,,),4)))
Excel solution 12 for Top Three Sales by Date, proposed by Md. Zohurul Islam: 
=LET(a,A2:A20,b,B2:B20,c,C2:C20,
d,SUMIFS(c,a,a,b,b),
e,LARGE(UNIQUE(d),SEQUENCE(3)),
f,XMATCH(d,e),
g,SORT(UNIQUE(FILTER(HSTACK(a,b,d),ISNUMBER(f))),3,-1),
h,VSTACK(HSTACK("Name",B1:C1),g),
h)
Excel solution 13 for Top Three Sales by Date, proposed by Pieter de B.: 
=LET(a,A2:C20,i,INDEX,s,SORT,u,UNIQUE,x,SUMIFS(i(a,,3),i(a,,1),i(+a,,1),i(a,,2),i(+a,,2)),s(u(FILTER(HSTACK(A2:B20,x),XMATCH(-x,s(u(-x)))<4)),,-1))
Excel solution 14 for Top Three Sales by Date, proposed by Asheesh Pahwa: 
=LET(
    c,
    A2:A20,
    uc,
    UNIQUE(
        c
    ),
    d,
    B2:B20,
    ud,
    UNIQUE(
        d
    ),
    a,
    C2:C20,
    r,
    DROP(
        REDUCE(
            "",
            uc,
            LAMBDA(
                x,
                y,
                VSTACK(
                    x,
                    LET(
                        f,
                        FILTER(
                            B2:C20,
                            c=y
                        ),
                        t,
                        TAKE(
                            f,
                            ,
                            1
                        ),
                        d,
                        DROP(
                            REDUCE(
                                "",
                                UNIQUE(
                                    t
                                ),
                                LAMBDA(
                                    a,
                                    v,
                                    VSTACK(
                                        a,
                                        HSTACK(
                                            v,
                                            SUM(
                                                FILTER(
                                                    TAKE(
                                                        f,
                                                        ,
                                                        -1
                                                    ),
                                                    t=v
                                                )
                                            )
                                        )
                                    )
                                )
                            ),
                            1
                        ),
                        IFNA(
                            HSTACK(
                                y,
                                d
                            ),
                            y
                        )
                    )
                )
            )
        ),
        1
    ),
    TAKE(
        SORT(
            r,
            {3,
            1},
            {-1,
            1}
        ),
        4
    )
)
Excel solution 15 for Top Three Sales by Date, proposed by Asheesh Pahwa: 
=LET(a,A2:A20&"-"&B2:B20,u,UNIQUE(a),m,MAP(u,LAMBDA(x,SUM((C2:C20)*(a=x)))),h,HSTACK(TEXTSPLIT(u,"-"),TEXTAFTER(u,"-"),m),TAKE(SORT(h,{3,1},{-1,1}),4))
Excel solution 16 for Top Three Sales by Date, proposed by ferhat CK: 
=LET(a,UNIQUE(A2:A20&"-"&B2:B20),b,BYROW(a,LAMBDA(x,SUMPRODUCT((A2:A20&"-"&B2:B20=x)*(C2:C20)))),c,BYROW(LARGE(UNIQUE(b),TOCOL({1,2,3})),LAMBDA(x,TEXTJOIN(";",,FILTER(a,b=x)))),REDUCE({"Name","Date","Amount"},c,LAMBDA(x,y,VSTACK(x,LET(q,TEXTSPLIT(y,,";"),r,TEXTBEFORE(q,"-"),t,--TEXTAFTER(TEXTSPLIT(y,,";"),"-"),HSTACK(r,t,XLOOKUP(r&"-"&t,a,b)))))))
Excel solution 17 for Top Three Sales by Date, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert: 
=LET(j,MAP(A2:A20,B2:B20,LAMBDA(a,b,TEXTJOIN(",",,a,TEXT(b,"dd.mm.yyyy")))),LET(x,LET(m,MAP(UNIQUE(j),LAMBDA(c,SUM(FILTER(C2:C20,c=j)))),TEXTSPLIT(TEXTJOIN(,,MAP(TOCOL(LARGE(UNIQUE(m),{1,2,3})),LAMBDA(p,TEXTJOIN(,,LET(q,MAP(m,UNIQUE(j),LAMBDA(o,i,XLOOKUP(p,o,i))),FILTER(q,NOT(ISNA(q))))&"/")))),",","/",TRUE)),HSTACK(x,MAP(MAP(TAKE(x,,1),TAKE(x,,-1),LAMBDA(a,b,TEXTJOIN(",",,a,b))),LAMBDA(c,XLOOKUP(c,UNIQUE(j),MAP(UNIQUE(j),LAMBDA(c,SUM(FILTER(C2:C20,c=j))))))))))
Excel solution 18 for Top Three Sales by Date, proposed by Imam Hambali: 
=LET(a, SORT(GROUPBY(A2:B20,C2:C20,SUM,0,0),3,-1), b, TAKE(UNIQUE(DROP(a,,2)),3), c, FILTER(a, BYROW(TAKE(a,,-1)=TRANSPOSE(b),OR)), VSTACK(E1:G1,c))
Excel solution 19 for Top Three Sales by Date, proposed by CA Raghunath Gundi: 
=LET(
    a,
    SORT(
        GROUPBY(
            A2:B20,
            C2:C20,
            SUM,
            0,
            0
        ),
        3,
        -1
    ),
    
    b,
    TAKE(
        UNIQUE(
            CHOOSECOLS(
                a,
                3
            )
        ),
        3
    ),
    
    FILTER(
        a,
        ISNUMBER(
            MATCH(
                CHOOSECOLS(
                a,
                3
            ),
                b,
                0
            )
        )
    )
)
Excel solution 20 for Top Three Sales by Date, proposed by Stefan Alexandrov: 
=LET(
    _groupBy,
    GROUPBY(
        A2:B20,
        C2:C20,
        SUM,
        ,
        0
    ),
    
    _amount,
    CHOOSECOLS(
        _groupBy,
        3
    ),
    
    _rank,
    MMULT(
        N(
            _amount
Excel solution 21 for Top Three Sales by Date, proposed by Hussain Ali Nasser: 
=LET(
nd,UNIQUE(A2:B20),
sm,SUMIFS(C2:C20,A2:A20,TAKE(nd,,1),B2:B20,TAKE(nd,,-1)),
SORT(FILTER(HSTACK(nd,sm), sm>LARGE(UNIQUE(sm),4)),3,-1)
)
Excel solution 22 for Top Three Sales by Date, proposed by Hussain Ali Nasser: 
=LET(
 x, GROUPBY(A2:B20, C2:C20, SUM, , 0),
 s, TAKE(x, , -1),
 SORT(FILTER(x, s > LARGE(UNIQUE(s), 4)), 3, -1)
)
Excel solution 23 for Top Three Sales by Date, proposed by Tomasz Jakóbczyk: 
=LET(a,SORT(PIVOTBY(A2:B20,,C2:C20,SUM,0,0),3,-1),FILTER(a,CHOOSECOLS(a,3)>=LARGE(UNIQUE(CHOOSECOLS(a,3)),3)))
Excel solution 24 for Top Three Sales by Date, proposed by Fausto Bier: 
=LET(r,SORT(GROUPBY(A2:B20,C2:C20,SUM,,0),3,-1),t,TAKE(r,,-1),TAKE(r,XMATCH(LARGE(UNIQUE(t),3),t)))
Excel solution 25 for Top Three Sales by Date, proposed by JORGE MANUEL MOSTACERO ASENCIO: 
=LET(U;UNICOS(A2:B20;FALSO);UC;BYROW(U;LAMBDA(X;CONCAT(X)));A;MAP(UC;LAMBDA(F;SUMA(FILTRAR(C2:C20;(A2:A20&B2:B20)=F))));N;K.ESIMO.MAYOR(UNICOS(A);3);R;ORDENAR(APILARH(U;A);3;-1;);FILTRAR(R;ELEGIRCOLS(R;3)>=N))

Solving the challenge of Top Three Sales by Date with Python

Python solution 1 for Top Three Sales by Date, proposed by Konrad Gryczan, PhD: 
import pandas as pd
path = "624 Top 3 Highest Sales.xlsx"
input = pd.read_excel(path, usecols="A:C", nrows=20)
test = pd.read_excel(path, usecols="E:G", nrows=4).rename(columns=lambda x: x.split('.')[0])
summary = input.groupby(['Customer', 'Date'], as_index=False)['Amount'].sum()
summary['Rank'] = summary['Amount'].rank(method='dense', ascending=False)
result = summary[summary['Rank'] <= 3].sort_values(by=['Amount', 'Date'], ascending=[False, True]).drop(columns='Rank').reset_index(drop = True)
result = result.rename(columns={'Customer': 'Name'})
print(result.equals(test)) # True

Solving the challenge of Top Three Sales by Date with Python in Excel

Python in Excel solution 1 for Top Three Sales by Date, proposed by Aditya Kumar Darak 🇮🇳: 
data = xl("A1:C20", True)
grp = (
 data.groupby(["Customer", "Date"])
 .sum()
 .sort_values("Amount", ascending=False)
 .reset_index()
)
large = grp["Amount"].drop_duplicates().nlargest(3)
result = grp[grp["Amount"].isin(large.values)]
result

Solving the challenge of Top Three Sales by Date with R

R solution 1 for Top Three Sales by Date, proposed by Konrad Gryczan, PhD: 
library(tidyverse)
library(readxl)
path = "Excel/624 Top 3 Highest Sales.xlsx"
input = read_excel(path, range = "A1:C20")
test = read_excel(path, range = "E1:G5")
result = input %>%
 summarise(Amount = sum(Amount), .by = c(Customer, Date)) %>%
 filter(dense_rank(desc(Amount)) <= 3) %>%
 arrange(desc(Amount), Date) 
all.equal(result, test, check.attributes = FALSE)
#> [1] TRUE

&&

Leave a Reply