Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted

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
Highlighted
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;

Highlighted

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

Highlighted
Contributor III
Contributor III

in a Chart

Highlighted
Contributor III
Contributor III

pivot table

Highlighted

Like this?

Capture.PNG

Expression used

=If(SecondaryDimensionality() = 0,

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

Sum(Value))

View solution in original post

Highlighted
Contributor III
Contributor III

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

Highlighted

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?

Highlighted
Contributor III
Contributor III

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

Highlighted

Try this may be

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