I am trying to do basically a vlookup in Excel from the results of 1 column of a company to another inside a straight table.
The following test expression works:
max(total aggr(if(rank(sum([TotalP&L]),1,1)>=60, sum([TotalP&L])),CompanyName))
Where I have randomly entered '60' as a rank to match to... as expected it returns the same sum([TotalP&L]) for all rows of the table.
What I would really like to do is change that 60 to the results of another expression in that table.
I would think something like the below would work, but no luck.
max(total aggr(if(rank(sum([TotalP&L]),1,1)>=[OtherExpression], sum([TotalP&L])),CompanyName))
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|
(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!
Thank you for taking the time to respond.
I know how to do this in the load script, I need the ability to implement it in a chart object so the users can select date ranges as well as customer groups.
Attached is a real kludgy solution. It requires that the chart be sorted by Purchases Rank for which I've created a hidden column. I then used the top() function to reference a specific row.
,rank(TOTAL sum([Total Time Spent (m)]))
I'm sure (hope) there's a better way.