topic Re: Finding absolute maximum value in QlikView

Finding absolute maximum value

BudQVdev — Sat, 16 Nov 2024 01:52:05 GMT

Hi,

For each dimension value, I would like to show the value of the measure which has the largest absolute value.

Dimension Date Measure

A 11/04 -3

A 11/05 2

A 11/06 1.5

B 11/04 1

B 11/05 2

B 11/06 4

C 11/04 -5

C 11/05 4

C 11/06 3

I'd like to have a chart show the results as below:

Dimension Largest Absolute value

A -3

B 4

C -5

I added a new column in the script, AbsMeasure, and I’ve been trying the below set analysis expressions to get the above results, but neither seems to be doing the job: they seem to be aggregating on a global level, not for each dimension value:

=max( {< AbsMeasure = {"$(= max(AbsMeasure),Dimension)"} >} Measure)

=max( {< AbsMeasure = {"$(=max(aggr(max(AbsMeasure),Dimension)))"} >} Measure)

Can you please help me what I did wrong.

Thank you

Re: Finding absolute maximum value

Anil_Babu_Samineni — Sun, 24 Nov 2019 16:19:07 GMT

May be create one field like

T1:
LOAD *, If(Measure>=0, 1, 0) as Len_Measure Inline [
Dimension , Date , Measure
A , 11/04 , -3
A , 11/05 , 2
A , 11/06 , 1.5
B , 11/04 , 1
B , 11/05 , 2
B , 11/06 , 4
C , 11/04 , -5
C , 11/05 , 4
C , 11/06 , 3
];

In Tabular Chart, you can use

Dimension : Dimension

Expression :

Sum(Aggr(If(Len_Measure=1, Max(Fabs(Measure)), Max({<Len_Measure={0}>} Fabs(Measure))*-1), Dimension))

Re: Finding absolute maximum value

BudQVdev — Sun, 24 Nov 2019 18:53:56 GMT

Thank you for your help.

But I don't think this solution is entirely correct: if there is a negative value, it will always come up as the abs max, even if there is a positive number with a higher absolute value.

I've added a new row to the data:

A , 11/07 , 4

For "A", the absolute max should be 4, but it's still showing -3.

I think I've managed to find the right solution with this formula, attaching the results:

Sum(Aggr(If( min(Measure) >= 0, max(Measure), if( max(Measure) > -min(Measure), max(Measure), min(Measure) ) ), Dimension) )

Re: Finding absolute maximum value

BudQVdev — Tue, 26 Nov 2019 13:39:35 GMT

Sorry, I wanted to attach this QV.

On another note, does anyone know if it is planned to introduce a new aggregation function to QlikView for returning the largest value in absolute terms?