1 of 1 people found this helpful
I made a bogus calc that sets "Tables" to zero. Whether or not it's a top-4, it just sets it to zero. (I could have gotten fancier and did a window min of the top 4 and subtracted 1 or something. Point is, I forced this calc to be the smallest of the top-4.)
On Sheet 1(2) I filter to index <= 4. And I made a RANK calc to rank the top 4 (as indicated by index), ranking on my bogus calc. This moves Tables to the bottom (if it's even in there.)
Finally, on Sheet 1(3) I moved that RANK calc so that it sorts the 4 rows. You don't ever have to display the bogus top-4Sles calc. I just did that to show my work here.
Thank you so much for help! I believe "Table" sub-category is appearing within top 4 category for each customer and the Rank top 4 calculation works.
But in the cases when the ranking for example "Furnishings" is at 5th, how can i make this appear at 4th Rank?
Is there anyway to show Top 4 only if "Furnishings" belongs to a customer with Furnishings always "4th Rank" otherwise Top 3 Category for each customer?
This is deceptively tricky
see the attached
I can't get everything you want - maybe Joe can figure it out
First the Customer must have a record for Tables or tables will not show up
Second I can force the record in the top 5 but I can get them to sort
I started with an LOD for the sales
then created 2 sets on subcategories
then outer joined the sets
and created a series of filters - see the viz on how the are set
set the rank like this
You are on 10.5 - in 2018.3 Action sets were introduced - they allow you to dynamically change the set members
I played with action sets to on a single customer at a time and had some success sorting - but that only showed 1 customer name
and honestly that was on an early attempt and I did not go back there
Maybe Joe can figure out how to sort them the way you want - the problem I ran into is the combined set actions create another partition in the data and messes with the sort
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.
I need to understand more.
I shoved "Table" to position 4 only if it appeared in the original top-4. But I can force it at the 4th no matter where it falls, if you want.
Now you ask about "Furniture". Is the target value for this exercise going to change? How will it be selected? (For instance, will the user select the value from a parameter?) We can do that too.
And yes, we can display only top-3 if there is no "Furniture" at all.
So tell me what you're really aiming for, and we can make it happen.
Thanks Joe and Jim for your help with this. I really appreciate it!
Yes Joe - I need "Tables" to appear at 4th position no matter where it falls (even it falls at 10th ranking for example). The reason I was changing the exercise to "Furniture" from "Tables" because "Tables" was always appearing within top 4 and "Furniture" was appearing in other ranking for ie 7th rank. It does not matter, we can stick to "Tables" for this exercise- (whichever you prefer) but need to it to appear at 4th Position no matter where it falls.
and yes need to display Top 3 if there is no "Tables" otherwise need to display Top 4 (if there is Tables for that customer). Please let me know if this is still confusing.
I'm getting back to this. Hang on.
OK. Step by step.
On Sheet 1(3) I added another calc. See [Does this customer have Tables]. This is a LOD that tells me if the customer has any Tables rows. If the value is greater than zero, there are Tables for this customer. This is a FIXED LOD. That means it will operate no matter what filters you have on the sheet. So even if you happen to filter out "Tables" for some reason, you would still know that the customer has Tables in his data.
Go to Sheet (4).. First of all, I have filtered this down to just a few customers. Some have no tables. Some have Tables in the top-4 already. Some have Tables below 4. So we can test all the conditions.
And I tool off the INDEX calc. I made my own RANK calc here. In the long run it arrives at the same values at your INDEX did, but I control what is actually getting ranked, and it doesn't depend on any other sheet sort ordering. And note the table calc setting I used. Restart every Customer. Every table calc I have added to these sheets uses that setting.
Next I created a calc to determine what position 4 holds. See [What is in position4 sales]. I'll use this to force Tables to sort to the 4th position later on.
Next I made a new version where I force a new sort value, similar to my old [Top-4 Sales]. See [Top-4 Sales (copy)]. This is the key step in the way I approached this. If the sub category is Tables, do things to force Tables to the 4th position. If Tables is less than 4, then make the sorting values one less than the current 4th position. If it's past the 4th position, bump it in front of whatever is in the 4th.
You can see the results of that on Sheet (4). And finally I made a new rank of the calc that pushes Tables to the 4th position. And you can see that where Tables exists, its number is always 4.
Go to Sheet (5). I made one more calc to display top-4 (or top-3 if there are no tables.) At this point you could remove [Rank Sales] from ROWS, and move [Rank Top-4 Sales] into that place, and the sub-categories would sort by that ranking value. You don't have to display any of the interim top-4 calcs. JUst leave SUM(Sales) on the sheet, and all the other calcs will just work behind the scenes.
Thank you Jim! I tried this and was working for me - I added the following rank calculation to get the sorting in the correct order - so that tables will always come last.... RANK(IF MIN([Sub Category]) = 'Tables' THEN -1 else SUM([Sales]) end)
Thank you Joe for your help!! I am taking a look at this to understand a bit more.