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)
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.