Hi Yuri! Could you please tell when are you going to show the solution cause its very interesting case!
Thanks in advance
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.
Please find the attached.
EBT_Top_N_by_Year.twbx 1.3 MB
Yuri, thank you for such good explanation. It helps a lot.
Nina, you're welcome.
Michael, you're welcome.