Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

caccio88
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:

Untitled4.png

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

Here attacched my qvw.

9 Replies
MVP
MVP

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

maybe ranking

1.png

if(

rank(-

(

Sum({<DataMonthYear ={">=$(=Date(addmonths(Max(DataMonthYear), 0), 'MMM-YY')) <=$(=Date(addmonths(Max(DataMonthYear),0),  'MMM-YY'))"}>}

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

-

Sum({<DataMonthYear ={">=$(=Date(addmonths(Max(DataMonthYear), -12), 'MMM-YY')) <=$(=Date(addmonths(Max(DataMonthYear),-12),  'MMM-YY'))"}>}

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

)

)=1

or

rank(

(

Sum({<DataMonthYear ={">=$(=Date(addmonths(Max(DataMonthYear), 0), 'MMM-YY')) <=$(=Date(addmonths(Max(DataMonthYear),0),  'MMM-YY'))"}>}

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

-

Sum({<DataMonthYear ={">=$(=Date(addmonths(Max(DataMonthYear), -12), 'MMM-YY')) <=$(=Date(addmonths(Max(DataMonthYear),-12),  'MMM-YY'))"}>}

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

)

)=1,

Sum({<DataMonthYear ={">=$(=Date(addmonths(Max(DataMonthYear), 0), 'MMM-YY')) <=$(=Date(addmonths(Max(DataMonthYear),0),  'MMM-YY'))"}>}

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

-

Sum({<DataMonthYear ={">=$(=Date(addmonths(Max(DataMonthYear), -12), 'MMM-YY')) <=$(=Date(addmonths(Max(DataMonthYear),-12),  'MMM-YY'))"}>}

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

)

caccio88
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
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

1.png

caccio88
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
MVP

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

yes in sort tab

fabs is the absolute value1.png

caccio88
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
MVP

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

if(

rank(

fabs(

Sum({<DataMonthYear ={">=$(=Date(addmonths(Max(DataMonthYear), 0), 'MMM-YY')) <=$(=Date(addmonths(Max(DataMonthYear),0),  'MMM-YY'))"}>}

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

-

Sum({<DataMonthYear ={">=$(=Date(addmonths(Max(DataMonthYear), -12), 'MMM-YY')) <=$(=Date(addmonths(Max(DataMonthYear),-12),  'MMM-YY'))"}>}

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

)

)<=3,

Sum({<DataMonthYear ={">=$(=Date(addmonths(Max(DataMonthYear), 0), 'MMM-YY')) <=$(=Date(addmonths(Max(DataMonthYear),0),  'MMM-YY'))"}>}

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

-

Sum({<DataMonthYear ={">=$(=Date(addmonths(Max(DataMonthYear), -12), 'MMM-YY')) <=$(=Date(addmonths(Max(DataMonthYear),-12),  'MMM-YY'))"}>}

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

)

caccio88
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?

caccio88
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)))

What do you think about?

It works, but maybe you have a better solution.

Community Browser