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

    Tanmay Thakur

      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.