-
1. Re: Getting topN results based on nested dimensions and using topN results with reversed hierarchy
EDIT: your description was fine, I just had to read it a few times.
You can include the total for the category ignoring the LOD on the sheet using LOD calculation, and just leverage the existing filter for Sub-Category and State for the purpose of identifying TopN:
{ FIXED [Sub-Category]: sum([Profit])}
-
2. Re: Getting topN results based on nested dimensions and using topN results with reversed hierarchy
Hi Justin, good question. Yes, the top 5 sub-categories may be different from state to state.
For the second worksheet, I'd like to show the profit in each state for each sub-category for the top 5 sub-categories in a selected state from the first worksheet.
-
3. Re: Getting topN results based on nested dimensions and using topN results with reversed hierarchy
sorry, edited my comment, cuz I realized what you were doing by looking closer.
Take a look at edited response above:
-
4. Re: Getting topN results based on nested dimensions and using topN results with reversed hierarchy
Hi Justin, thanks a lot for your answer.
The <Profit Total for Sub-Category> LOD calculated field is calculating the sum of profit for each sub-category, regardless of states. If I'd like to show the profit for each sub-category in each state, and show it for the top N sub-categories from a given state, is there a way to do it?
For example: in the first worksheet, the top 3 sub-categories with the highest profit in Arizona are Machines, Accessories, and Phones. Is there a way to show the profit for each of these three sub-categories in each of the states selected in the second worksheet, like the A- and B- states?
-
5. Re: Getting topN results based on nested dimensions and using topN results with reversed hierarchy
1 of 1 people found this helpfulOk, so you want to choose a specific state, calculate the TopN subcategories for that state, then show the total state-by-state Profit for just those categories in your chosen state.
In that case, I would go with a different approach.
1) create a Parameter based on the state field (right clicke State>Create>Parameter)
2) Create a calculated field IsChosenState: [State] = [Choose State] //<that's the parameter
3) Create a set based on the Sub-Category Field, using the 'Top' Tab and set a calculation to limit the profit to the state selected:
4) Drop into your view state, Subcategory, and sum(profit) like before. Drop the Set into filter, and put IsStateChosen before State, sorted desc and hide headers, so the chosen state shows up on top.
4.a) if you really want to use different sheets, just use the isStateChosen calculation in the filter, set to true on one sheet and false on the other.
-
6. Re: Getting topN results based on nested dimensions and using topN results with reversed hierarchy
Thank you for the detailed answer, Justin!
I tried it and it worked well for the superstore data/workbook.
I have not gotten it to work when I applied it to my own workbook, which has other dimensions in the hierarchy. I am going to experiment a bit more based on the approach you suggested.
I am marking this question as answered.
