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

[caccio88]

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.

[maxgro]

maybe ranking

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]

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?

[maxgro]

it seems you can sort by  fabs of your expression descending

[caccio88]

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

[maxgro]

yes in sort tab

fabs is the absolute value

[caccio88]

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?

[maxgro]

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]

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]

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.