Sure, I cannot provide my .qvd, but the below is a watered down example.
Say you have customers and you want to look at the time spent with them vs the $ they spend and you have a straight table that looks like:
| Customer Name | Total Purchases | Purchases Rank | Total Time Spent (m) | Time Spent Rank | Time Spent $ Lookup |
| Customer1 | $150 | 6 | 30 | 7 | $100 |
| Customer2 | $75 | 9 | 35 | 6 | $150 |
| Customer3 | $200 | 5 | 15 | 10 | $50 |
| Customer4 | $50 | 10 | 20 | 9 | $75 |
| Customer5 | $300 | 2 | 25 | 8 | $90 |
| Customer6 | $375 | 1 | 40 | 5 | $200 |
| Customer7 | $250 | 3 | 60 | 2 | $300 |
| Customer8 | $225 | 4 | 90 | 1 | $375 |
| Customer9 | $100 | 7 | 55 | 3 | $250 |
| Customer10 | $90 | 8 | 50 | 4 | $225 |
(In the above chart example only the 'Customer Name' field is a dimension. The others are all calculated and need to be calculated, due to the users's need to adjust date ranges and customer types the chart has to be dynamic.)
The highlighted column is the one that is giving me issues. I want to be able to say Customer8 takes the #1 amount of time, which corresponds to spending $375 (based on our #1 spender).
From my example formula above translating it into this chart:
max(total aggr(if(rank(sum([Purchases]),1,1)>=1, sum([Purchases])),[Customer Name])) will return $375
The question is how to translate that hard coded 1 to the [Time Spent Rank] expression to correctly return the corresponding [Total Purchases] of that [Time Spent Rank].
This is so simple in Excel, but it has given me a real tough time in QlikView. Thank you in advance for any help!
