topic Set Analysis but with a dimension for answer in App Development

Set Analysis but with a dimension for answer

Sat, 14 Mar 2015 18:22:01 GMT

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!

Re: Set Analysis but with a dimension for answer

Anonymous — Sun, 15 Mar 2015 00:47:07 GMT

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.

Re: Set Analysis but with a dimension for answer

Sun, 15 Mar 2015 06:17:31 GMT

Thanks a million time Boris!

It works!

Re: Set Analysis but with a dimension for answer

Sun, 15 Mar 2015 18:26:54 GMT

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!

Re: Set Analysis but with a dimension for answer

Anonymous — Mon, 16 Mar 2015 13:03:11 GMT

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

Re: Set Analysis but with a dimension for answer

Mon, 16 Mar 2015 13:53:52 GMT

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.