QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

New Contributor III

Dimension limit with the largest and the smallest value in the same chart

Hi everybody,

what I would like is to display at the same time the largest and smallest value at the same time.

That's an example:

At the end I would like to sort by the "absolute value". Is that clear?

Here attacched my qvw.

9 Replies
MVP

Re: Dimension limit with the largest and the smallest value in the same chart

maybe ranking

if(

rank(-

(

[Importo Rata Corretto]*([Importo Carico]/[Importo EM TOT NO IVA_Corretto])/1000000)

-

[Importo Rata Corretto]*([Importo Carico]/[Importo EM TOT NO IVA_Corretto])/1000000)

)

)=1

or

rank(

(

[Importo Rata Corretto]*([Importo Carico]/[Importo EM TOT NO IVA_Corretto])/1000000)

-

[Importo Rata Corretto]*([Importo Carico]/[Importo EM TOT NO IVA_Corretto])/1000000)

)

)=1,

[Importo Rata Corretto]*([Importo Carico]/[Importo EM TOT NO IVA_Corretto])/1000000)

-

[Importo Rata Corretto]*([Importo Carico]/[Importo EM TOT NO IVA_Corretto])/1000000)

)

New Contributor III

Re: Dimension limit with the largest and the smallest value in the same chart

Hi maxgro‌ thanks for your contribute.

Using your formula in the expression it's impossible to display the other possible dimension too.

What I would like to do is to sort by absolute value. So in my case the order is |1,5| ; |0,6| ; |0,3| ; |0,0|.

Clearer now?

MVP

Re: Dimension limit with the largest and the smallest value in the same chart

it seems you can sort by  fabs of your expression descending

New Contributor III

Re: Dimension limit with the largest and the smallest value in the same chart

What do u mean by "fabs"?

I have written the expression that you have recommended but it works like your first picture.

Your suggestion is to work in the sort function? How?

thx

MVP

Re: Dimension limit with the largest and the smallest value in the same chart

yes in sort tab

fabs is the absolute value

New Contributor III

Re: Dimension limit with the largest and the smallest value in the same chart

Nice! With "fabs" the sort works, but if I would like to show only for the 3 largest values in the dimension limits and than others seems doesn't work anymore.

Do you have any suggestion about?

MVP

Re: Dimension limit with the largest and the smallest value in the same chart

if(

rank(

fabs(

[Importo Rata Corretto]*([Importo Carico]/[Importo EM TOT NO IVA_Corretto])/1000000)

-

[Importo Rata Corretto]*([Importo Carico]/[Importo EM TOT NO IVA_Corretto])/1000000)

)

)<=3,

[Importo Rata Corretto]*([Importo Carico]/[Importo EM TOT NO IVA_Corretto])/1000000)

-

[Importo Rata Corretto]*([Importo Carico]/[Importo EM TOT NO IVA_Corretto])/1000000)

)

New Contributor III

Re: Dimension limit with the largest and the smallest value in the same chart

That solve the "fabs" sorting, but is not possible to display the sum of "others". In this case only |0,0|.

is it clear what I mean?

New Contributor III

Re: Dimension limit with the largest and the smallest value in the same chart

This is my solution to display something like "others"...

Dimension:

=aggr(if(rank(fabs(Sum(Value)))<=5,Dimension),Dimension)

No flag "suppress when value is null"

Expression:

sum(Value)

Sort:

rank(fabs(Sum(Value)))