topic Re: Max value in QlikView

Max value

eptaqlik — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Max value

thannila — Wed, 07 Nov 2018 11:51:29 GMT

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;

Re: Max value

sunny_talwar — Wed, 07 Nov 2018 12:04:20 GMT

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

Re: Max value

eptaqlik — Wed, 07 Nov 2018 12:41:45 GMT

in a Chart

Re: Max value

eptaqlik — Wed, 07 Nov 2018 12:42:31 GMT

pivot table

Re: Max value

sunny_talwar — Wed, 07 Nov 2018 12:48:30 GMT

Like this?

Expression used

=If(SecondaryDimensionality() = 0,

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

Sum(Value))

Re: Max value

eptaqlik — Wed, 07 Nov 2018 13:29:28 GMT

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

Re: Max value

sunny_talwar — Wed, 07 Nov 2018 13:35:09 GMT

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?

Re: Max value

eptaqlik — Wed, 07 Nov 2018 13:46:18 GMT

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

Re: Max value

sunny_talwar — Wed, 07 Nov 2018 13:57:22 GMT

Try this may be

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

Re: Max value

eptaqlik — Wed, 07 Nov 2018 14:05:20 GMT

Many tks Sunny,

your solution works wery well.

Great job.

Re: Max value

eptaqlik — Wed, 07 Nov 2018 14:23:41 GMT

Hi Sunny,

I set this solution as you into the example you attached and it is the best solution; I like also the other solution you send to me. Sorry because into the first time I said that this is not exact.

Ciao

Marco