Set Analysis but with a dimension for answer

[#1]

Hello everyone,

I'm just starting with Qlik Sense and it's my first message that I leave, so thanks in advance for your help and insight.

I'm making a text box, in which, I would like to show first of all the Highest unit Price paid for an item, followed by the name of the customer who paid that price, the period and year, in which that sale occurred. I got the max(UnitPrice) Ok, but it's the name of customer and date that I can't find. I found the formula Set Analysis when your looking for a measure, but what about the Dimension info related to my max(UnitPrice).

Again thanks for your help!

[Anonymous · Accepted solution]

Hi tremblay,

check this post out - this for QlikView but luckily expressions and calculations are the same in Sense:

Displaying the Top Salesperson in a Textbox

Let me know if it makes sense or your need further directions.

[#3]

Thanks a million time Boris!

It works!

[#4]

Thanks again Boris!

I succeeded with Highest and lowest price to get the country name and MonthYear, but using the same expression, I can't get it for for my highest export in $ as show:

Here's my expressions for the Country:

     firstsortedvalue (country, -Aggr(Max[ExpVal],country))

     // ExpVal = Export Value in US$

     And my expressions for the MonthYear (MM-YYYY):

     firstsortedvalue (MonthYear, -Aggr(Max[ExpVal],MonthYear))

     // ExpVal = Export Value in US$

I also tried with the expression:

only(if(aggr(Rank(Sum([Sales Amt])),Salesperson)=1,Salesperson))

But I get the same empty result.

Thanks for you collaboration!

[Anonymous]

Hi tremblay,

your expression seems to be right, but check what you see if you replace Only with concat(distinct - I have a suspicion that you get more than one value for some reason and Only will return null in that case.

this is an expression you can try to see top 3 sales persons:

=concat(distinct

  IF(

  aggr(

  rank(sum(Sales)),

  Salesperson

  ) <=3 ,

  Salesperson&CHR(13)))

Also make sure that your field names are in correct case - i got burnt many times because of that

[#6]

Thanks again Boris for your insight!

I'll try that ...

Concerning the case, yes, I got cut too! So I try to cut & Paste as much as possible, but I will double check.