1 Reply Latest reply on Apr 3, 2018 9:24 AM by Patrick A Van Der Hyde

# How to retrieve Nth smallest/largest element at different levels of granularity

Suppose I have following hierarchy A > B > C > D > Date > Amount in my date

where (A,B,C,D) is considered as a unique combination.

I want to find the nth and (n+1) smallest "sum(Amount)" at a "Date" level for every unique (A,B,C,D) combination. Such that the final result looks like this (different levels of details)

1. A || B || C || D || nth smallest Amount || (n+1)th smallest amount || Some calculation involving both the amounts
2. A || B || C || nth smallest Amount || (n+1)th smallest amount || Some calculation involving both the amounts
3. A || B || nth smallest Amount || (n+1)th smallest amount || Some calculation involving both the amounts
4. A || nth smallest Amount || (n+1)th smallest amount || Some calculation involving both the amounts

My final output would look like this. I used rank function and "compute using" dates within pane. However the moment I filter out the dates for null values the ranks mess up. How to preserve ranks?

Can someone help me. Let me know if I need to be more clear.