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: 
eptaqlik
Contributor III
Contributor III

Max value

Hi,

please can anyone help me?

I have value for Serie and Industry (Foglio1).

I want a report with the sum of the value for any Serie and for any Industry.

But I also want a column with the name of the Industry that have the max value for any Serie.(Foglio3)

Please see attached file with some data and the result that I want.

Many tks in advance.

Marco

1 Solution

Accepted Solutions
sunny_talwar

Like this?

Capture.PNG

Expression used

=If(SecondaryDimensionality() = 0,

FirstSortedValue(Industry, -Aggr(Sum(Value), Serie, Industry)),

Sum(Value))

View solution in original post

11 Replies
thannila
Creator
Creator

data:

LOAD Serie,

     Industry,

     sum(Value) as value

FROM

(ooxml, embedded labels, table is Foglio1) group by Serie, Industry;


right join(data)

data1:

load Serie,

max(value) AS value

resident data group by Serie;




data2:

Generic

LOAD Serie,

   Industry,

    sum(Value) as value

FROM

(ooxml, embedded labels, table is Foglio1) group by Serie, Industry;

sunny_talwar

Where are you looking to see this result? In a chart or text box object?

eptaqlik
Contributor III
Contributor III
Author

in a Chart

eptaqlik
Contributor III
Contributor III
Author

pivot table

sunny_talwar

Like this?

Capture.PNG

Expression used

=If(SecondaryDimensionality() = 0,

FirstSortedValue(Industry, -Aggr(Sum(Value), Serie, Industry)),

Sum(Value))

eptaqlik
Contributor III
Contributor III
Author

Yes, the result is that you have explained into your picture but with this espression I have as  a result another column for all the Industry with the same name of the Industry

this is the image with my real data; I want for all the industry of the serie only the max; ex: Serie TEXPRTP7K = Valore Max GENERAL INDUSTRY

maxValue.PNG

sunny_talwar

Would you be able to share a sample of what you have? Max Industry name needs to update for all the rows within a single serie?

eptaqlik
Contributor III
Contributor III
Author

Sorry Sunny, I can't share the real data; Yes, max Industry name in all rows within a single serie because I want to define my product serie in which industry have max market

Legami_database.PNG

sunny_talwar

Try this may be

FirstSortedValue(TOTAL <Serie> Industry, -Aggr(Sum(Value), Serie, Industry))