6 Replies Latest reply on Oct 20, 2017 7:18 AM by KK Edla

# Multi Level sorting for Mutilple dimensions in crosstab (Year split by Month)

I'm stuck with some weird requirement. I tried multiple ways and researched on the tableau community and on internet, but could find an answer.

Req:

I need to find  Sales for Top 2 Regions and Top 2 Categories in each of those regions and Top 3 Subcategories for each Category by Calendar Year (broken by month). And ROW total for each subcategory

Issue:

In the attached workbook

NoDate - Shows exactly what I need

WithDate - This is the duplicate of NoDate workbook, but added Month(Order Date). -- This doesn't give me the correct results

Thanks,

KK

• ###### 1. Re: Multi Level sorting for Mutilple dimensions in crosstab (Year split by Month)

I just solved it in 10.2 - you won't be able to open the workbook. Adding my write-up instead. Please hold!

• ###### 2. Re: Multi Level sorting for Mutilple dimensions in crosstab (Year split by Month)

Hi KK

you sent a twb workbook we need a twbx workbook with the data

Jim

• ###### 3. Re: Multi Level sorting for Mutilple dimensions in crosstab (Year split by Month)

This was a really tricky one. It may have been able to be solved other ways, but I approached it from an order of operation perspective.

Firstly, at the highest order of operation, we want to only include the top 2 regions. I achieved this with:

RANK_DENSE(sum({FIXED [Region] : SUM([Sales])}))

Compute using table down, filter to top 2.

Next, I only wanted the top 3 categories.

RANK_DENSE(sum({INCLUDE [Region],[Category] :SUM({EXCLUDE [Order Date] : SUM([Sales])})}))

Compute using region, filter to top 3 for top 3 categories (because there are only 3 categories, this was implied in this dataset.)

Next, the tricky part. Top 3 sub-categories.

RANK_DENSE(sum({INCLUDE [Region],[Category],[Sub-Category] :SUM({EXCLUDE [Order Date] : SUM([Sales])})}))

Note - I use rank_dense to be thorough. It's always best practice to know which rank you're using.

This one was hard - head to 'edit table calculation' and compute using>specific dimension and partition by month and sub-category at the deepest level, restarting>none.

I had to exclude order date in the calculation, but include it in the specific dimension to have the LOD calculation pick it up to exclude it. Totally counterintuitive!

let me know if that works!

• ###### 4. Re: Multi Level sorting for Mutilple dimensions in crosstab (Year split by Month)

Hey Jim - the workbook was connected to superstore. Easy to point to the data source, but great point! Always best practice to include data.

• ###### 5. Re: Multi Level sorting for Mutilple dimensions in crosstab (Year split by Month)

Hi Jim,

Thanks @jim_dehner for the response. I wanted to attach the .twbx, but it was too large of a file (>120 MB). So thought i'll go ahead with .twb as the data source is very familiar to all of us.

• ###### 6. Re: Multi Level sorting for Mutilple dimensions in crosstab (Year split by Month)

Hi Galen,

Thank you very much for your time and efforts on this. Couple of things to mention.

1. When trying to filter categories to 2, it doesn't work. Always displays all 3 in the view.
2. The moment I added ROW grand totals, the whole thing messes up, it all ok whacky.