Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Referencing Expression in AGGR

Help Please!!!

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

6 Replies
rustyfishbones
Master II
Master II

do you have some sample data

Not applicable
Author

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 NameTotal PurchasesPurchases RankTotal Time Spent (m)Time Spent RankTime Spent $ Lookup
Customer1$1506307$100
Customer2$759356$150
Customer3$20051510$50
Customer4$5010209$75
Customer5$3002258$90
Customer6$3751405$200
Customer7$2503602$300
Customer8$2254901$375
Customer9$1007553$250
Customer10$908504$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!

Not applicable
Author

Anyone have any thoughts on this?  Still an outstanding issue for me.

nagaiank
Specialist III
Specialist III

An implementation using script is attached. You may follow similar process for your actual project.

HTH.

Not applicable
Author

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.

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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.

=top(TOTAL

sum([Total Purchases])

,rank(TOTAL sum([Total Time Spent (m)]))

)

I'm sure (hope) there's a better way.

-Rob