Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

liviumac
New Contributor III

How to return the field value corresponding to a calculation

Hello people, would appreciate your help

Suppose I have a data structure which contains just 3 fields: Client, Region and Sales

I want to display, using a single formula, the Region with the highest sales

I now how to calculate the sales of the Region with the highest sales:

max(aggr(sum(Sales),Region))

but how can I also display the Region that the above value corresponds to?

of course I can do this using a simple table with region and sales with a limitation set to 1 in the dimension field, but I need to insert this information in the title of a gauge

your help is much appreciated

thank you

1 Solution

Accepted Solutions
agigliotti
Honored Contributor II

Re: How to return the field value corresponding to a calculation

try:

Only( aggr( if( sum(Sales) = max( total aggr(sum(Sales),Region)), Region, null() ), Region ) )

4 Replies
agigliotti
Honored Contributor II

Re: How to return the field value corresponding to a calculation

try:

Only( aggr( if( sum(Sales) = max( total aggr(sum(Sales),Region)), Region, null() ), Region ) )

antoniotiman
Honored Contributor III

Re: How to return the field value corresponding to a calculation

FirstSortedValue(DISTINCT Region,-Aggr(Sum(Sales),Region))

Regards,

Antonio

liviumac
New Contributor III

Re: How to return the field value corresponding to a calculation

yes that worked, fantastic!

I am now trying to understand the formula.

thank you very much

liviumac
New Contributor III

Re: How to return the field value corresponding to a calculation

@Antonio Mancini

your formula also works

thank you very much

Community Browser