6 Replies Latest reply on Oct 22, 2019 2:11 PM by Yuri Fal

# Educational Brain Teaser : Top N Customers by Year

Hi friends,

Teaser again, this time about finding Top N Customers within each Year.

"Pizza cake, bro" -- one could tell -- "just take a look there":

https://kb.tableau.com/articles/howto/finding-the-top-n-within-a-category

No Table Calc pills -- no INDEX(), no RANK(), no nothing.

There is a Set pill on Filters (not a Table Calc based one).

And the Grand Total looks as expected.

Hope you'll find the teaser entertaining enough.

If not (enough), you may have a bonus track ;-)

Yours,

Yuri

PS  I'll be back with the answer at the end of next week.

• ###### 1. Re: Educational Brain Teaser : Top N Customers by Year

Hi Yuri! Could you please tell when are you going to show the solution cause its very interesting case!

• ###### 2. Re: Educational Brain Teaser : Top N Customers by Year

Hi Nina,

I'm a bit late with the answer, however here it is:

There [Top N by Year] Set on Filters is a combo of the two Sets (call them 'Branches'):

each (of those 'Branches') is a combo of another two Sets (call them 'Leaves'):

each (of those 'Leaves') is just a plain old Top N Set

by SUM([Sales]) aggregate, but filtered to the particular Year:

Last but not least, a distinct Calculated Field is required to identify only those rows in the data,

for which the Customer is from one of the Top N 'Leaf' Sets AND the Sales is for the particular Year:

That's it. No magic, no silver bullet, just a boring cumbersome B-Tree made from the Combo Sets.

Q: How many objects (Calculated Fields & Sets) do we need to build the same for -- say -- 12 Months?

A: It would need 12 Calculated Fields (Sum of Sales for each Month), plus 12 'Leaf' Top N Sets,

plus 12 'Branch' Combo Sets (6+3+2+1) at four different 'Levels' -- as the INT( log2(12) ) + 1 gives 4.

Plus the one latter calculation [Sales Top N by Month].

It's obvious that the approach is a brute force, and it has an upper limit of usefulness

at about 8-16 distinct Dimension members (Years in the example) to GROUP BY.

Yours,

Yuri

1 of 1 people found this helpful
• ###### 3. Re: Educational Brain Teaser : Top N Customers by Year

Yuri, thank you for such good explanation. It helps a lot.

• ###### 4. Re: Educational Brain Teaser : Top N Customers by Year

Nina, you're welcome.

• ###### 5. Re: Educational Brain Teaser : Top N Customers by Year

Mind: BLOWN!

Thanks, Yuriy!

• ###### 6. Re: Educational Brain Teaser : Top N Customers by Year

Michael, you're welcome.